Problem with Error:Object Required

[kismet]

I can't figure out why this error keeps popping out when I run my code. It always says that frmImmunizationRecord cannot be "found" by VB.

Function decreaseStockOnHand()
Dim stock As Integer
Dim computedStock As Integer
Dim strSQL As String
Dim vaccineCode As String


If [Forms]![frmImmunizationRecord]![Vaccine] Is Not Null And [Forms]![frmImmunizationRecord]![p1BrandName] Is Not Null Then <<<****ERROR IN THIS LINE****

DoCmd.SetWarnings False

vaccineCode = DLookup(&quot;[VaccineID]&quot;, &quot;tblVaccine&quot;, &quot;[VaccineName] =&quot; & [Forms]![frmImmunizationRecord]![Vaccine] And &quot;[BrandName]=&quot; & [Forms]![frmImmunizationRecord]![p1BrandName])

stock = DLookup(&quot;[StockOnHand]&quot;, &quot;tblVaccine&quot;, &quot;[VaccineID] = vaccineCode&quot

computedStock = stock - 1

strSQL = &quot;UPDATE tblVaccine SET StockOnHand = &quot; & computedStock & &quot; WHERE VaccineID = vaccineCode&quot;

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If

End Function

How should I remedy this? Is my syntax correct? The If-statement is supposed to check that the 2 fields (vaccine and brand name) in frmImmunizationRecord are not null, and if so, continue on with the computation.

Thank you! =)

 

[gransbpa]

Hi Kismet,

It seems to be that your code should look like this:

If ((Not IsNull([Forms]![frmImmunizationRecord]![Vaccine])) Or ([Forms]![frmImmunizationRecord]![Vaccine]))<> &quot;&quot And ... same thing for the other field.
But from the error message you might also draw the conclusion that your form is not open, so you should check that one also.

Good luck!

 

[kismet]

Hi! thanks for the fast reply!

I followed your code already but that error still keeps appearing. The frmImmunizationRecord is definitely open as this is where this function is called. I am getting desperate already...what seems to be the defect of my code?

 

[gransbpa]

Hi Kismet,

I saw I made an error, the 'or' in the first line should be an 'and' (also for the other field). You could try again (see lines below), but it does not seem to be the problem. When it does not help, could you post back the exact error number and message Access generates when you run the code? This info might be useful.

If ((Not IsNull([Forms]![frmImmunizationRecord]![Vaccine])) And ([Forms]![frmImmunizationRecord]![Vaccine]))<> &quot;&quot And ... same thing for the other field.

 

[kismet]

Hi again gransbpa! I manipulated your code a bit and there's no problem with it =). The error seems to be coming from another line of the code(specified below). &quot;Error 2001: You cancelled the previous operation &quot; appears. We are almost there (thanks to you!).


Function decreaseStockOnHand()

Dim stock As Integer
Dim computedStock As Integer
Dim strSQL As String
Dim vaccineCode As String


If ((Not IsNull([Forms]![frmImmunizationRecord]![invisibleVaccine])) And (Not IsNull([Forms]![frmImmunizationRecord]![invisibleBrand]))) Then

DoCmd.SetWarnings False

vaccineCode = DLookup(&quot;[VaccineID]&quot;, &quot;tblVaccine&quot;, &quot;([VaccineName] = [Forms]![frmImmunizationRecord]![invisibleVaccine] And [BrandName]= [Forms]![frmImmunizationRecord]![invisibleBrand])&quot

stock = DLookup(&quot;[StockOnHand]&quot;, &quot;tblVaccine&quot;, &quot;[VaccineID] = vaccineCode&quot <<<***ERROR SEEMS TO BE HERE. The vaccineCode got the proper value in the previous statement but can't seem to pass the value in THIS statement for the DLookup to work right.***

computedStock = stock - 1

strSQL = &quot;UPDATE tblVaccine SET StockOnHand = &quot; & computedStock & &quot; WHERE VaccineID = vaccineCode&quot;

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If

Thank you very much! Sorry for nagging all of you. =)

 

[gransbpa]

You're welcome

now I see what the problem is. Your DLookup statement should be something like

DLookup(&quot;[StockOnHand]&quot;, &quot;tblVaccine&quot;, &quot;[VaccineID] = '&quot; & vaccinecode & &quot;'&quot, but you probably already found out

Good luck