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

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Hi all,

I was trying not to have to ask on here and find out the solution myself but it's now got to the point of needing help!

I have a form for components (frmComponents) (each component has its own record). The form has a subform (fsubComponentHistory) to keep track of each component's history (recevied, sent, returned faulty, used for product, etc).
I am adding functionality in terms of Batch numbers whereby I click on a button on frmComponent and it opens a new form with openargs and in dialog mode (frmComponentBatch) where all the new batch information is entered and then on clicking a Save/Close button, it runs an insertinto query and closes the form. The subform fsubComponentHistory then shows the newly entered details.

I am almost there but getting one little niggle in that I cannot get a combo box on the subform (Batch) to update - it doesn't display the new batch number in the combo box and when clicking on it, it hasn't been populated either.

However, on re-opening the form, the combo box then displays the value I wanted to be there, so it seems like it's just not requeried properly.

I've tried different iterations of a requery command in different areas of the code but to no avail.

I think it must be something to do with the fact that on fsubComponentHistory, the "Batch" combo box has 4 columns and the one being displayed is column(2) (the 3rd column). But I don't know how to reference this in the INSERT INTO vba query.


Here is the relevant code for frmComponent for the button "btnAddNewBatch"

Code:
Private Sub btnAddNewBatch_Click()
    
    'strLinkCriteria = "Component = " & Me.idsComponentID
    'MsgBox strLinkCriteria 'for debugging purposes, display the link criteria
    
    DoCmd.OpenForm "frmComponentBatch", , , , acFormAdd, acDialog, 5
    Me.fsubComponentHistory.Requery
End Sub
here is the code for the save/close action of frmComponentBatch

Code:
Private Sub btnSaveClose_Click()
    Dim strINSERT As String
    
    Select Case OpenArgs
        Case Is = 5
            
            'Add the history element to the component
            strINSERT = "INSERT INTO tblComponentHistory (dtmDate, Component, Batch, History, Quantity, Comments, Supplier, blnUpdate)" _
                        & " VALUES (#" & Format(Now(), "mm/dd/yyyy") & "#, '" _
                        & Me.Component.Value & "', '" _
                        & Me.ComponentBatchNo.Value & "', " _
                        & "1, " _
                        & Me.QtyReceived.Value & ", '" _
                        & Me.Comments.Value & "', '" _
                        & Me.Supplier.Value & "', " _
                        & "-1);"
                
            Debug.Print strINSERT 'for debugging purposes
                
            CurrentDb.Execute strINSERT
                       
    End Select
    
    strINSERT = "" 'cleanup
    
    Forms!frmComponent!fsubComponentHistory.Controls!Batch.Requery
    
    DoCmd.Close
    
End Sub
and for completeness, here is the code for the OnLoad of frmComponentBatch

Code:
Private Sub Form_Load()
    Dim strBatch As String
    Dim strBatchText As String
    Dim strBatchNewNumber As String
    Dim lngzBatchNumber As Long
    Dim lngzBatchNewNumber As Long
    Dim strBatchNew As String
    Dim strSELECT As String
    Dim strWHERE As String
    Dim strSQL As String
    Dim rstBatch As Recordset
    
            
    strSELECT = "SELECT LAST (ComponentBatchNo) As LastBatchNo FROM tblComponentBatch"
    strWHERE = " WHERE Component = " & Form_frmComponent.idsComponentID & ";"
    strSQL = strSELECT & strWHERE
    Debug.Print strSQL
    
    Set rstBatch = Nothing
    Set rstBatch = CurrentDb.OpenRecordset(strSQL)
    
    If Not rstBatch("[LastBatchNo]").Value = Null Then
    
        strBatch = rstBatch("[LastBatchNo]").Value
        Debug.Print strBatch
    
        'get all non numerical characters from existing bacth number
        strBatchText = getNonNumeric(strBatch)
        Debug.Print strBatchText
    
        'get only the numerical values from the existing batch number
        lngzBatchNumber = getNumeric(strBatch)
        Debug.Print lngzBatchNumber
    
        'Increment the numerical value element
        lngzBatchNewNumber = lngzBatchNumber + 1
        Debug.Print lngzBatchNewNumber
    
        'takes a number (8) and converts to 0008 as a string
        strBatchNewNumber = String(4 - Len(CStr(lngzBatchNewNumber)), "0") & CStr(lngzBatchNewNumber)
        Debug.Print strNatchNewNumber
    
        'add the non-numerical characters and the newly incremented number together into a new variable
        strBatchNew = strBatchText & strBatchNewNumber
        Debug.Print strBatchNew
    
        'automatically populate form with values when loaded
        Select Case (OpenArgs)
    
            Case Is = 5
                Me.Component.Value = Form_frmComponent.idsComponentID
                Me.BatchReceived.Value = Date
                Me.ComponentBatchNo = strBatchNew

    
        End Select
    
    Else
        Me.Component.Value = Form_frmComponent.idsComponentID
        Me.BatchReceived.Value = Date
        
    End If
    
    Set rstBatch = Nothing 'clean up
    
End Sub
I really look forward to hearing where I'm going wrong!
I'll be in the office tomorrow so will be able to try things then, unless you post before 5:30pm.

Many thanks in advance.
Simon
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
A couple of things, when referring to controls you don't need .Value to get or set the value - it's the default property , and will save you a bunch of typing.

I assume it's this line that isn't working ?
Code:
Forms!frmComponent!fsubComponentHistory.Controls!Batch.Requery

If it is the syntax is
Code:
Forms!frmComponent!fsubComponentHistory.Form![COLOR="Red"]YourcontrolName[/COLOR].Requery

I can't quite fathom what your combobox is called form your code.
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
You have the correct combobox name in your code, yes, it's "Batch" (probably should be cboBatch, but I was being lazy).

Trying that slight change as I type...

Nope it's not updating the combo box still.

Thank you for correcting the syntax though.
 

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
Hmmm, What's the record source for the combo box?
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Hmmm, What's the record source for the combo box?

I was just typing a reply saying I've actually just had a thought about this!

The recordsource is tblComponentHistory, but the rowsource is qryComponentBatch, which doesn't reference that particular table.

I think I'm going to have to look at this tomorrow!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:57
Joined
Feb 28, 2001
Messages
18,735
It is possible to manually requery a control in some cases, and a combo box DOES have that particular method available to it (according to my Object Browser.) If you do a .Requery method on the box, it permits you to force any changes to be noticed in the .RowSource's implied recordset.

The only trick will therefore be to know in your code WHERE to place the .Requery, but I'm betting that it would work best just after the place where you did the operation that would update the box's row source, whatever it is.
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
It is possible to manually requery a control in some cases, and a combo box DOES have that particular method available to it (according to my Object Browser.) If you do a .Requery method on the box, it permits you to force any changes to be noticed in the .RowSource's implied recordset.

The only trick will therefore be to know in your code WHERE to place the .Requery, but I'm betting that it would work best just after the place where you did the operation that would update the box's row source, whatever it is.

Thank you for your reply @The_Doc_Man, I have tried putting it in different places, including right after the insertinto code but to no avail.

Investigating further I think it must be something to do with the rowsource and control source, all the other combo boxes.

The subform's recordsource I've changed to qryComponentHistory, which is a replica of tblComponentHistory, but this hasn't changed anything sadly.

The row source of the particular combo box, however, is just

Code:
qryComponentBatch

and its control source is "Batch". The other combo boxes have their control source as a select query, such as the "history" combo box being:

Code:
SELECT [tblHistory].[idsHistoryID], [tblHistory].[HistoryType] FROM tblHistory ORDER BY [HistoryType];

So maybe I need to amend it so that it's just a short select query? But I don't think I do.

for completeness, qryCompoentBatch's SQL is:

Code:
SELECT tblComponentBatch.idsComponentBatchID, tblComponentBatch.Component, tblComponentBatch.ComponentBatchNo, tblComponentBatch.QtyReceived, tblComponentBatch.DateReceived, tblComponentBatch.Supplier, tblComponentBatch.Comments
FROM tblComponent INNER JOIN tblComponentBatch ON tblComponent.idsComponentID = tblComponentBatch.Component
ORDER BY tblComponentBatch.ComponentBatchNo, tblComponent.ComponentName;
 

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
Your update query is updating tblComponentHistory , but combo batch's data is only pulled from tblComponentBatch and tblComponent.

I can't see what there is to update in combo batch?
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Yeah, I think it has to be something to do with that. I'm looking in to that now.

Thank you.

Edit: I think what I was thinking originally is that if I'm inputting the data in to frmComponentBatch, when I'm typing it in, then when I requery the batch combo box in the subform of the other form, the data should already be in tblComponentBatch, in order for the requery to pull it from - does that make any sense?
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
If the ComponentBatch record isn't saved at the time you requery, then the record won't show up when in cmbBatch.
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Ok, I've got a little further - I've managed to get the new batch number to be available in the combo box now, by forcing a record save to tblComponentBatch when first clicking on the Close button.

The magic bit of save code I used to force a write to the table whilst keeping the form still open was:

Code:
DoCmd.RunCommand (acCmdSaveRecord)
I've just seen Minty, that you've replied whilst I'm typing this, so I'm going to see what you've written...

It's still not appearing though, but I'm getting there.

Edit: Ha! it looks like I was going down the right path then, considering your reply.
 

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
You can use the Dirty property of a record to force a save;

If Me.Dirty then Me.Dirty = False

Will save a record that has been changed.
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
So now, I think maybe I need to get the sub form to pull the batch number from the table in a different way rather than trying to update that particular control by using an insert into query.

Everything else will work as an insert into, but not the batch combo.

would that make sense do you think?
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
You can use the Dirty property of a record to force a save;

If Me.Dirty then Me.Dirty = False

Will save a record that has been changed.

Thanks,

yes I saw that somewhere else, so will give it a go before trying something completely different.
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Yep, still not working so trying something else now.

Here's the modified code for the Click event of the close button:

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

However, it's now giving me the following error on the line before "End Select":

property let procedure not defined and property get procedure did not return an object

Could be because it's a continuous form.

Without the column(2) bit, it says that the value isn't valid for this field, which made me think it was trying to enter the text string in to an autonumber id field.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:57
Joined
Oct 17, 2012
Messages
3,260
It is possible to manually requery a control in some cases, and a combo box DOES have that particular method available to it (according to my Object Browser.) If you do a .Requery method on the box, it permits you to force any changes to be noticed in the .RowSource's implied recordset.

Any control can be requeried.

If the control has its own data source (list box, combo box, subform, OLE object) or has a control source containing a domain function, then just that control gets requeried; otherwise the entire form gets requeried.
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Ok, I think I'm getting there but I'm getting a data conversion error. I want to reference column2 of the combo box in the recordset but I can't find the syntax for want of trying...

My code now looks like this:

Code:
Private Sub btnSaveClose_Click()
    Dim strINSERT As String
    Dim strSQL As String
    Dim rsCompHist As Recordset
       
    Select Case OpenArgs
        Case Is = 5
                    
            If Me.Dirty Then Me.Dirty = False 'code to force a save of the record.
                                           
            'Add the history element to the component
            strINSERT = "INSERT INTO tblComponentHistory (dtmDate, Component, History, Quantity, Comments, Supplier, blnUpdate)" _
                        & " VALUES (#" & Format(Now(), "mm/dd/yyyy") & "#, " _
                        & Me.Component & ", " _
                        & "1, " _
                        & Me.QtyReceived & ", '" _
                        & Me.Comments & "', " _
                        & Me.Supplier & ", " _
                        & "-1);"
                
            MsgBox strINSERT 'for debugging purposes
                
            CurrentDb.Execute strINSERT
            
            strSQL = "SELECT * FROM tblComponentHistory;"
            Set rsCompHist = CurrentDb.OpenRecordset(strSQL)
            With rsCompHist
                .MoveLast
                If .Updatable Then
                    .Edit
                    ![Batch] = "'" & Me.ComponentBatchNo & "'"
                    .Update
                    .Close 'close table
                End If
            End With
            'Forms!frmComponent!fsubComponentHistory.Form!Batch.Column(2) = Me.ComponentBatchNo
            
            Set rsCompHist = Nothing
    
    End Select
    
    
    strINSERT = "" 'cleanup
    
    DoCmd.Close
    
    
End Sub

The line that bombs out is the one saying

![Batch] = "'" & Me.ComponentBatchNo & "'"

I've tried ![Batch].column(2) amd ![Batch.column(2)] but neither work.

Any ideas?

column(0) = is an id field
column(1) is a component name field
column(2) is the batch number and it's this I'm interested in.
 

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
You can't update the recordset for the combo dynamically like that. Recordsets for comboboxes are not editable, they are a snapshot.

I can't quite picture what you are trying to do - could you post up a picture of your form(s) and draw some arrows pointing at what should be updating what?
 

Mr_Si

Registered User.
Local time
Today, 10:57
Joined
Dec 8, 2007
Messages
163
Yes sure.

Here is the order of events:









When checking in tblComponentBatch, however, the data is all there to be pulled from:

 

Minty

AWF VIP
Local time
Today, 10:57
Joined
Jul 26, 2013
Messages
7,797
Okay - that makes more sense. A couple of things - why make the batch number a combo box at all?

It looks like it is a fixed bit of data for that component / history line. If it's just that then make the combo a textbox and alter it's control source accordingly, I can't see much need for the batch number as a combo in this form, maybe on another one where you want to see everything from a specific batch would make more sense.
 

Users who are viewing this thread

Top Bottom