Find data in form in another table (XP)

Topic

New Reply

I have a form, frmperminfo, which has various demographic info including name and SSN. On this form is a button to open the Needs Assessment form. What I want to do is have Access look out and see if the SSN on frmperminfo has a corresponding record in the Needs Assessment table. If it does, then open the needs assessment form with that person’s SSN. If there is not a record for the person yet, then open up a blank form, but automatically put in the first name, last name and SSN from perminfo. I hope this makes sense and I tried to fiddle with macros and conditions. The closest I got was with Dlookup, but couldn’t figure out how to get that to come up with true or false value.

Thanks for any help!

Jodi

Reply | Quote

Replies

Jodi

Here’s some code I use for this same purpose.
I have highlited the table names and relevent info to my needs for you to change to yours.

Hope this helps:-

Private Sub cmdComments_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strValue As String
Set db = CurrentDb
strSQL = “Select * From tblcomments Where tblComments.estimateno = ” & Forms!frmDetails!EstimateNo
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
DoCmd.OpenForm “frmcomments”
‘DoCmd.GoToRecord acDataForm, “frmcomments”, acNewRec
Forms!frmComments!EstimateNo.SetFocus
Forms!frmComments!EstimateNo = Forms!frmDetails!EstimateNo ‘Add extra lines if you need
Else
strValue = Forms!frmDetails!EstimateNo
DoCmd.OpenForm “frmComments”, acViewNormal, , “EstimateNo = “ & strValue
End If
End Sub

Reply | Quote

This is exactly what I need, however, I’m getting an error and hope you can help. Here is the code I put:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strValue As String
Set db = CurrentDb
strSQL = “Select * From Needs Assessment Where Needs Assessment.SID=” & Forms!frmperminfo!txtSIDHidden
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
DoCmd.OpenForm “Needs Assessment”
DoCmd.GoToRecord acDataForm, “Needs Assessment”, acNewRec
Forms!Needs_Assessment!Grammar.SetFocus
Forms!Needs_Assessment!First_Name = Forms!frmperminfo!txtFirstName
Forms!Needs_Assessment!Last_Name = Forms!frmperminfo!txtLastName
Forms!Needs_Assessment!SID = Forms!frmperminfo!txtSID
Else
strValue = Forms!frmperminfo!txtSID
DoCmd.OpenForm “Needs Assessment”, acViewNormal, “SID =” & strValue
End If
End Sub

When I click on Command 155, I get an error that says Syntax Error (missing operator) in query expression ‘Needs Assessment.SID = 999999999’. Then it asks me to debug and it highlights the line that starts with Set rst =. BTW 999999999 is the correct SSN coming up. I’m wondering if it’s getting confused because my table and form are named the same thing. Do I need to put something after From to let it know that Needs Assessment is a table? I appreciate your help. I only know enough about VB to get myself in trouble.

Jodi

Reply | Quote

Jodi
Your naming convention will invariably cause you problems, spaces in form names and table names are not regarded highly in Access.
Try the following:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strValue As String
Set db = CurrentDb
strSQL = “Select * From [Needs Assessment] Where [Needs Assessment].SID=” & Forms!frmperminfo!txtSIDHidden
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
DoCmd.OpenForm “Needs Assessment”
DoCmd.GoToRecord acDataForm, “Needs Assessment”, acNewRec
Forms!Needs_Assessment!Grammar.SetFocus
Forms!Needs_Assessment!First_Name = Forms!frmperminfo!txtFirstName
Forms!Needs_Assessment!Last_Name = Forms!frmperminfo!txtLastName
Forms!Needs_Assessment!SID = Forms!frmperminfo!txtSID
Else
strValue = Forms!frmperminfo!txtSID
DoCmd.OpenForm “Needs Assessment”, acViewNormal, “SID =” & strValue
End If
End Sub

Check references also thet you have DAO 3.6 ticked.

Reply | Quote

Jodi
Your naming convention will invariably cause you problems, spaces in form names and table names are not regarded highly in Access.
Try the following:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strValue As String
Set db = CurrentDb
strSQL = “Select * From [Needs Assessment] Where [Needs Assessment].SID=” & Forms!frmperminfo!txtSIDHidden
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
DoCmd.OpenForm “Needs Assessment”
DoCmd.GoToRecord acDataForm, “Needs Assessment”, acNewRec
Forms!Needs_Assessment!Grammar.SetFocus
Forms!Needs_Assessment!First_Name = Forms!frmperminfo!txtFirstName
Forms!Needs_Assessment!Last_Name = Forms!frmperminfo!txtLastName
Forms!Needs_Assessment!SID = Forms!frmperminfo!txtSID
Else
strValue = Forms!frmperminfo!txtSID
DoCmd.OpenForm “Needs Assessment”, acViewNormal, “SID =” & strValue
End If
End Sub

Check references also thet you have DAO 3.6 ticked.

Reply | Quote

Sorry, try this instead:

Select * From Needs_Assessment Where Needs_Assessment.SID=” & Forms!frmperminfo!txtSIDHidden”

Reply | Quote

Sorry, try this instead:

Select * From Needs_Assessment Where Needs_Assessment.SID=” & Forms!frmperminfo!txtSIDHidden”

Reply | Quote

This is exactly what I need, however, I’m getting an error and hope you can help. Here is the code I put:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strValue As String
Set db = CurrentDb
strSQL = “Select * From Needs Assessment Where Needs Assessment.SID=” & Forms!frmperminfo!txtSIDHidden
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
DoCmd.OpenForm “Needs Assessment”
DoCmd.GoToRecord acDataForm, “Needs Assessment”, acNewRec
Forms!Needs_Assessment!Grammar.SetFocus
Forms!Needs_Assessment!First_Name = Forms!frmperminfo!txtFirstName
Forms!Needs_Assessment!Last_Name = Forms!frmperminfo!txtLastName
Forms!Needs_Assessment!SID = Forms!frmperminfo!txtSID
Else
strValue = Forms!frmperminfo!txtSID
DoCmd.OpenForm “Needs Assessment”, acViewNormal, “SID =” & strValue
End If
End Sub

When I click on Command 155, I get an error that says Syntax Error (missing operator) in query expression ‘Needs Assessment.SID = 999999999’. Then it asks me to debug and it highlights the line that starts with Set rst =. BTW 999999999 is the correct SSN coming up. I’m wondering if it’s getting confused because my table and form are named the same thing. Do I need to put something after From to let it know that Needs Assessment is a table? I appreciate your help. I only know enough about VB to get myself in trouble.

Jodi

Reply | Quote

Jodi

Here’s some code I use for this same purpose.
I have highlited the table names and relevent info to my needs for you to change to yours.

Hope this helps:-

Private Sub cmdComments_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strValue As String
Set db = CurrentDb
strSQL = “Select * From tblcomments Where tblComments.estimateno = ” & Forms!frmDetails!EstimateNo
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount = 0 Then
DoCmd.OpenForm “frmcomments”
‘DoCmd.GoToRecord acDataForm, “frmcomments”, acNewRec
Forms!frmComments!EstimateNo.SetFocus
Forms!frmComments!EstimateNo = Forms!frmDetails!EstimateNo ‘Add extra lines if you need
Else
strValue = Forms!frmDetails!EstimateNo
DoCmd.OpenForm “frmComments”, acViewNormal, , “EstimateNo = “ & strValue
End If
End Sub

Reply | Quote