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"
here is the code for the save/close action of frmComponentBatch
and for completeness, here is the code for the OnLoad of frmComponentBatch
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
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
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
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'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: