Fairly new to VBA. I have a list box on a form within Access which is populated with data from a table. Selecting a value from the list box gives and ID which is then used to perform a query. I need this ID to be available for use in another form to perform a query based on the Value. What is the best way of achieving this?
`Dim IDValue As String
IDValue = Me.lstBoxCompanyName.Value
CompDetailSQL = "SELECT * FROM Companies WHERE Companies.CompanyID = " & IDValue`
In a module
Dim IDValue AS Integer
Sub setIDValue(id As Integer)
IDValue = id
End Sub
Function getIDValue() As Integer
getIDValue = IDValue
End Function
Then from any of your code
'This will set you ID
setIDValue YOUR_VALUE
'This will retrieve the value
getIDValue
'so your code could be
setIDValue Me.Me.lstBoxCompanyName.Value
CompDetailSQL = "SELECT * FROM Companies WHERE Companies.CompanyID = " & getIDValue
Obviously this could use some Error Handling in the event that No IDValue is set. Also I used integer even though I see you are using a String the data type should be the same type as CompanyID so you can change Integer to String if needed but your will also have to change your query to SELECT * FROM Companies WHERE Companies.CompanyID = '" & getIDValue & "' because your query implies a number currently.
You can gain access to another forms controls by specifying the full path. Example :
Forms!MyFormsName!MyControlsName.Value
and
Forms!frmCustomer!CboCustomer.Column(0)
So if you want to access a value in VBA that is contained in another form and use it as part of a query it would look like:
CompDetailSQL = "SELECT * " & _
"FROM Companies " & _
"WHERE CompanyID = " & Forms!frmCustomer!lstBoxCompanyName.Value
in ms Access you can also simply use form_NAMEofFORM.NAMEofCONTROL
Example you can reference
dim myAmount as double
myAmount = form_receipt.total
to access the value showing on form receipt textbox total.
Dim IDValue As StringChange it toPublic IDValue As Stringand place it in a module.Public IDValue As String. Then you would assign a value to the public variable like you would normally, but it will be available for all forms to use.