• Find data in form in another table (XP)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Find data in form in another table (XP)

    Author
    Topic
    #393501

    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

    Viewing 1 reply thread
    Author
    Replies
    • #713904

      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

      • #714876

        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

        • #714881

          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.

        • #714882

          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.

        • #714907

          Sorry, try this instead:

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

        • #714908

          Sorry, try this instead:

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

      • #714877

        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

    • #713905

      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

    Viewing 1 reply thread
    Reply To: Find data in form in another table (XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: