Hi,
I have a search form1, and when user click "ComEdit2" button, it would open another form named "TalentEdit2", and when it loads, the result from search form 1 will go to related field.
It supposed to be easy since only return records from one field. But it keeps showing run-time error 3464 Data type mismatch in criteria expression and highlight
"Set RS = CurrentDb.OpenRecordset(strSQL)". I have been searching online and tried all different ways, just not working.
Also, I would like to know how I can have the field name auto change? Instead of Forms![TalentEdit2].test1, test2, test3, I can use Forms![TalentEdit2].testi,
so I can get rid off if statement.
My code below. Using Access 2016
Code:
Private Sub ComEdit2_Click()
DoCmd.OpenForm "TalentEdit2"
MyID = Me.TalentID
recNo = DLookup("[CatName]", "Category", "TalentID=" & MyID) ' --> trying to find how many record return so it can be use for i loop
Dim RS As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT CatName FROM Category WHERE TalentID = '" & MyID & "'"
Set RS = CurrentDb.OpenRecordset(strSQL)
For i = 1 To recNo
If i = 1 Then
Forms![TalentEdit2].test1 = RS!CatName
ElseIf i = 2 Then
Forms![TalentEdit2].test2 = RS!CatName
Else
Forms![TalentEdit2].test3 = RS!CatName
End If
Next i
End Sub
Thank you very much...