Adding data to a temp table

[sda7bobp]

I have a temp table, tblTempInv with 43 fields. This table is created each morning and inventory is added to it or subtracted from it numerous times each day. I need to be able to make those additions and subtractions in code to increase the security of the numbers, and prevent editing of the data after the drug and volume has been verified as that which was administered to the patient. The field names are mostly a concatenation of the DrugID (lngDrugID) and either a "V" or an "O" signifying full or open vials. When the user accepts the drug and dosage, I want an entry to be made which will change only the data in the 1 or two columns affected, but will bring the other numbers down from the previous record into the new record so the new record reflects the total inventory of all the drugs after the entry. I am having difficulty in referring to each of these columns in the old and new records and determining how to make the change(s) in the correct field(s). Should I use an array? How could this be done the most efficiently?
Thanks
Bob

 

[hkaing79]

I would use SQL's UPDATE statement.

 

[sda7bobp]

Thanks, hkaing79, will give it a try.
bob

 

[sda7bobp]

It is still not working. I get an error saying the SQL statement is not correct because it lacks a 'SELECT'...or 'UPDATE'
Here is the code:

Private Sub Yes()

Dim db As Database, rst As Recordset, intResponse As Integer
Dim lngDrugID As Long, lngCaseNum As Long, datDate As Date
Dim sngmgUsed As Single, sngmlUsed As Single, sngmlLeft As Single
Dim strFieldNameO As String, strFieldNameV As String, sngVial As Single
Dim varInv(42, 42) As Variant, v As Variant, u As Variant, tdf As TableDef, fld As Field


    lngDrugID = Me![subfrmOKDrugUsed]![txtDrugID]
    lngCaseNum = Me.txtCaseNum
    datDate = Me.txtDate
    sngmgUsed = Me![subfrmOKDrugUsed]![txtmgOrdered]
    sngmlUsed = Me![subfrmOKDrugUsed]![txtmlUsed]
    strFieldNameO = CStr(lngDrugID) & "O"
    strFieldNameV = CStr(lngDrugID) & "V"
    

Set db = CurrentDb

Set rst = db.OpenRecordset("tblTempInv")
rst.MoveLast
sngmlLeft = rst(strFieldNameO)
    
    If sngmlLeft > sngmlUsed Then
        sngmlLeft = sngmlLeft + DLookup("Size", "tblDrug1", "[DrugID] = " & lngDrugID) - sngmlUsed
        sngVial = rst(strFieldNameV) - 1
    ElseIf sngmlLeft <= sngmlUsed Then
        sngmlLeft = sngmlLeft - sngmlUsed
    End If
rst.Close

[COLOR=red]DoCmd.RunSQL "UPDATE tblTempInv" & _
"SET tblTempInv(strFieldNameO)= " & sngmlLeft & ", tblTempInv(sngFieldNameV)" = " & sngVial;" [/color]

Set rst = db.OpenRecordset("tblInvUsed")

    With rst:
        .AddNew
        !CaseNum = lngCaseNum
        !DrugID = lngDrugID
        !Date = datDate
        !mgUsed = sngmgUsed
        !mlUsed = sngmlUsed
        .Update
    End With
    rst.Close


End Sub

 

[PHV]

Why not simply this ?
Set rst = db.OpenRecordset("tblTempInv")
With rst
.MoveLast
.Edit
sngmlLeft = rst(strFieldNameO)
If sngmlLeft > sngmlUsed Then
sngmlLeft = sngmlLeft + DLookup("Size", "tblDrug1", "[DrugID] = " & lngDrugID) - sngmlUsed
rst(strFieldNameV) = rst(strFieldNameV) - 1
Else
sngmlLeft = sngmlLeft - sngmlUsed
End If
rst(strFieldNameO) = sngmlLeft
.Update
.Close
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hkaing79]

You need a space between tblTempInv and SET. You also need ' around your value if it's text. You don't need tblTempInv to precede your field name if it's unique. Also, I believe the correct format is

.[field], no?

Did you want to update all the values or just one? The UPDATE SQL you are now using updates your entire tblTempInv.

Could you explain a little more about tblTempInv? The MoveLast property is bugging me. Are you just storing one record into tblTempInv and updating it?

It looks like a lot of unnecessary coding.

 

[sda7bobp]

I'm really not sure why I have problems with this website, but I don't get all the replies to threads. I had to wait until I got home yesterday to get the last two posts.

hkaing79 asked about tblTempInv and the MoveLast. Actually that should have been removed when I used the Update statement. Originally I was going to make a new record for each entry in case I needed to check for problems in the system, but rethinking this now. The Update of the whole table would be necessary if only maintaining one record in that table, so that is fine.

PHV, I'll try the .Edit and see how that works, may be just as easy.

Thanks to all.
Bob