CODEWITHSUNDEEP
ms-accessvbams-access-2003
QtheNovice
QtheNovice

Reputation: 95

Object variable error

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?

Upvotes: 1

Views: 470

Answers (3)

QtheNovice
QtheNovice

Reputation: 95

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.

Upvotes: 0

Leptonator
Leptonator

Reputation: 3519

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];"

Upvotes: 1

HansUp
HansUp

Reputation: 97131

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];"

Upvotes: 1

Related Questions