I'm completely new to VBA and MS Access!
- I have a Subform within a form which contains data about items which all have an "Equipment ID" field.
- Using data from a different table, I have a query to count the number of "faults" associated with each item. This Query also has the "Equipment ID" field.
- I have a second form which contains text boxes, and can be accessed by a button. On the button click, the form opens up and data from the selected current row in the subform are passed into the text boxes. I have assigned the original data table to the form control source.
What I want to do:
- I'm having trouble with the query. I have been trying to link the subform and the query, so that when the row on the sub form is selected, the "Equipment ID" is grabbed from that, and the "number of faults" in the Query can be found from its corresponding "Equipment ID".
Here is the code so far (and what I want to happen in comments):
Private Sub Button_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim EqIDfrm As String 'Equipment ID from the subform
'Open up the Faults Query
Set db = CurrentDb
Set rs = db.OpenRecordset("qryFaultTotal")
'open up the second form
DoCmd.OpenForm "SecondForm"
'Grab the "Year" from the selected row in the sub form and assign it to SelectYear. The text
'box value in the second form then becomes the SelectYear value.
SelectYear = [Forms]![Form1]![tblAsset Subform].[Form]![Year]
[Forms]![Form2]![txtYear] = SelectYear
' etc. for other fields
'grab the equipment ID from the subform and from the Query
EqIDfrm = [Forms]![Form1]![tblAsset Subform].[Form]![Equipment_ID]
EqIDqry = rs![Equipment_ID]
'<--- this seems to be part of the problem
'Lookup the Total number of faults from the FaultTotal Query, where the Equipment ID in the Query is
the same as the equipment ID on the subform from the selected row.
Fault = DLookup("[TotalFault]", "[FaultTotal]", "[EqIDqry] = " & [EqIDfrm] & "")
End Sub
- When I use lookup I keep getting an error of some sort (type mismatch, run-time error), and I just can't figure it out. It doesn't seem to like the EqIDqry but I don't know why despite Googling. I feel like it should be very simple, but I understand I may have to do something totally different! Any advice would be appreciated :)
