1

I've got a subform (RegistrationFsub) based on table Registration. It's purpose is to create a one-to-many relationship between a person and the year(s) they have enrolled in the group.

When a mom enrolls in a new year, I have a command button that is supposed to add a new line to the table Registration with MomID (from the parent form: MomsFsub) and the start date of the current enrollment year (YearStart, from table Year). Since the current enrollment period is represented by the latest date, I want to use the Max() command to retrieve it from YearT. I tried the following code:

Dim db As DAO.Database
Dim sqlSTR As String
Dim IDvar As String
'new code added since question posted
Set db = CurrentDb

Call MsgBox (Max(YearT!YearStart), vbOKonly)
'MsgBox checks value returned for Max(YearStart)
'end new code

IDvar = CStr(MomID)
sqlSTR = "INSERT INTO Registration(YearStart, MomID) SELECT Max(YearStart), "_
   & IDvar & " AS expr1 FROM YearT;"
'new: debug statement
Debug.Print sqlSTR
db.Execute sqlSTR

And I got an "Object variable or With block variable not set" error. What am I doing wrong?

Edit: Setting the db to Currentdb fixes the Object variable error, but now returns a "Too few parameters" error. The original table name "Year" has been changed to "YearT," since I only reference it in this one bit of code anyway.

Update

Now that I've fixed RegistrationFsub, it seems that the button also inserts data currently displayed in other controls on the form. So if the 2012 entry has RID = 1 and Leader = True, the above code creates an entry in Registration that also has RID = 1 and Leader = True. How can I keep those other fields blank?

Improve this question
0

3 Answers 3

Reset to default
1

The object variable error is because you didn't Set db to anything before you attempted db.Execute. Do this first ...

Set db = CurrentDb

If you later get an error with Execute, it may be because Year is a reserved word. Enclose that table name in square brackets to avoid confusing the db engine.

sqlSTR = "INSERT INTO Registration(YearStart, MomID) SELECT Max(YearStart), "_
   & IDvar & " AS expr1 FROM [Year];"
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

For "too few parameters", add Debug.Print sqlSTR after the sqlSTR = ... line. Run the code and go to the Immediate window (Ctrl+g). Copy the SQL text, create a new query in the query designer, switch to SQL View, and paste in the SQL text. When you run that query, Access will pop up an input box asking you to supply a value for the parameter. That box also include the name of whatever Access thinks is the parameter. What is it?
When you said "copy the SQL text," did you mean "from the Immediate window? Because the Immediate window was completely blank. I never learned debugging, really. When I copied INSERT INTO Registration (YearStart, MomID) SELECT Max(YearStart), & IDvar & As expr1 FROM YearT; into the SQL query designer it asked for "YearStart," and resulted in two fields entitled Expr1000 (where it listed the YearStart value I entered) and expr1 (where it listed "& IDvar &"). Max(YearStart) is also causing errors in a MsgBox statement I added.
Okay, I figured out how to get Debug.Print to work - the first time, I'd put it after the execute command instead of before. Still getting the error for [YearStart]. I've double-checked the spelling and everything.
Just to be sure, both tables (Registration and Year) each include a field named YearStart. Is that what you meant?
Darn and blast. It's not called YearStart in table YearT. I was only checking Registration. That was totally my problem. (BTW - I realized I could change the table name to YearT easily, so I did.)
|
1

If you are adding a new record based on the Mom's current new entry, you need to take the current time: Now() and parse the year off of it..

Year(Now())

Looking for Max(YearStart) could be looking for a record that happened 5 years ago..

sqlSTR = "INSERT INTO Registration(YearStart, MomID) SELECT Max(YearStart), "_
   & IDvar & " AS expr1 FROM Year;"

I think you need to update the code to two different operations:

sqlSTR = "INSERT INTO Registration(Year(Now()), MomID)"

run your code.. Then do a..

sqlSTR= "SELECT Year(Now()), " & IDvar & " AS expr1 FROM [Year];"
Improve this answer

1 Comment

I didn't do it with Year(Now()) because I don't know how to make that work when a mom enrolls in January 2014, for instance, but the enrollment year is 2013 because the term runs through a school year. So I created Year to contain the start of each term. It currently contains 9/1/2012 and 9/1/2013 for the current year and last year.
0

The most relevant answer was deleted before it could be selected, so I shall paste the content here:

The object variable error is because you didn't Set db to anything before you attempted db.Execute. Do this first ...

Set db = CurrentDb

If you later get an error with Execute, it may be because Year is a reserved word. Enclose that table name in square brackets to avoid confusing the db engine.

sqlSTR = "INSERT INTO Registration(YearStart, MomID) SELECT Max(YearStart), "_
    & IDvar & " AS expr1 FROM [Year];"

For "too few parameters", add Debug.Print sqlSTR after the sqlSTR = ... line.

But before the Execute command

Run the code and go to the Immediate window (Ctrl+g). Copy the SQL text, create a new query in the query designer, switch to SQL View, and paste in the SQL text. When you run that query, Access will pop up an input box asking you to supply a value for the parameter. That box also include the name of whatever Access thinks is the parameter.

The trouble here is that YearStart had a different field name in table Year.

Many thanks for the clear and helpful answer.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.