VBA Insert into combo box and requery issue (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 12:38
Joined
Dec 8, 2007
Messages
163
Because it's a lookup from another table, it's made itself in to a combo box and I accepted it that way. But also, there are times when I need to enter stuff directly into the sub form and will need to choose which batch I'm using out of those which have been added in to the database.

So, I'd like to keep it as a combo for now.

Adding stuff to a database retrospectively always seems to bring issues for me!
 

Mr_Si

Registered User.
Local time
Today, 12:38
Joined
Dec 8, 2007
Messages
163
I had a brainwave on Friday on my cycle home. My code now works!

Code:
Private Sub btnSaveClose_Click()
    Dim strINSERT As String
    Dim numBatch As Long
    
       
    'pre-cleanup
    strINSERT = ""
    numBatch = 0
           
    Select Case OpenArgs
        Case Is = 5
                    
            If Me.Dirty Then Me.Dirty = False 'code to force a save of the record.
            
            numBatch = DMax("idsComponentBatchID", "tblComponentBatch")
            MsgBox numBatch
                                              
            'Add the history element to the component
            strINSERT = "INSERT INTO tblComponentHistory (dtmDate, Component, History, Quantity, Batch, Comments, Supplier, blnUpdate)" _
                        & " VALUES (#" & Format(Now(), "mm/dd/yyyy") & "#, " _
                        & Me.Component & ", " _
                        & "2, " _
                        & Me.QtyReceived & ", " _
                        & numBatch & ", '" _
                        & Me.Comments & "', " _
                        & Me.Supplier & ", " _
                        & "-1);"
            MsgBox strINSERT 'for debugging purposes
            CurrentDb.Execute strINSERT
            
    End Select
    
    'cleanup
    strINSERT = ""
    numBatch = 0
    
    DoCmd.Close
    
End Sub

it's opened as acdialog and the requery happens on the form close. Combo box is now working.
 

Users who are viewing this thread

Top Bottom