Assign Value to a Specific Field of a Record in a Table

[kismet]

Sorry, it is me again! =)
I am making an inventory database, and this concerns two tables- the tblVaccine (stores info about a Vaccine including its stock on hand) and the tblImmunization (records that contain the patient, vaccine, and date of vaccination). I want to automatically deduct 1 from the stock on hand of a specific vaccine when a vaccination happens. Problem is, how do I tell the database which vaccine's stock on hand to update (as there are many records of vaccines)?

My unfinished VB code is:
Function computeStockOnHand()

Dim test As String
Dim vaccine1 As String
Dim vaccine2 As String
Dim stock As Integer
Dim computedStock As Integer
Dim record As Integer



If [Forms]![frmImmunizationRecord]![Vaccine] Is Not Null And [Forms]![frmImmunizationRecord]![p1BrandName] Is Not Null Then
test = DLookup("[VaccineID]", "tblImmunization", "[Vaccine] =" & [Forms]![frmImmunizationRecord]![Vaccine] And "[p1BrandName]=" & [Forms]![frmImmunizationRecord]![p1BrandName])

stock = DLookup("[StockOnHand]", "tblVaccine", "[VaccineID]= test&quot
computedStock = stock - 1

****computedStock is the now the updated stock hand. HOW DO I ASSIGN THIS TO THE SPECIFIC RECORD I WANT TO UPDATE?****


End Function

Again, thank you very much for your patience. Any help would be greatly appreciated. =)

 

[Orion45]

I would use a string SQL statement to update the table. It should look something like this:

dim strSQL as string
docmd.setwarnings false
strSQL = "UPDATE tblVaccine SET Stock = "& stock &" WHERE VaccineID = "& VacID &" AND Brand = '"&  p1BrandName &"';"

From what you described your table must have a combined key of VaccineID and Vaccine Brand which create a unique instance. You will need a VacID variable as a Long Interger and a brand name variable as a string.
Hope this helps let me know if you need any clarification.

 

[Orion45]

oops, forgot the last half of that code!

docmd.runSQL strSQL
docmd.setwarnings True 'Turn warnings off and back on again to avoid annoying update warning.

 

[kismet]

Thank you Orion45 for the very helpful tip! I'll try this asap and let you know what happens.

again, thanks! =)

 

[kismet]

Orion45...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 again! =)

 

[Orion45]

If the vaccine field is on your main form the easiest way to reference it is to use the Me. operator.
ex.

Me.Vaccine.value

I think you will also need to fix your SQL statement to reconize your variables. This is done in the following formats;
&quot; & variable & &quot; number value
'&quot; & variable & &quot;' string value
#&quot; & variable & &quot;# date value
You will need to apply this to your where clause to associate it to a number.
WHERE VaccineID = &quot;& vaccineCode &&quot;;
Depending on what kind of data source you are connecting to you may need to end with a semicolon (.
Hope this helps!