Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Michigan City, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find data in form in another table (XP)

    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

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find data in form in another table (XP)

    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

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Michigan City, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find data in form in another table (XP)

    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

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find data in form in another table (XP)

    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.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find data in form in another table (XP)

    Sorry, try this instead:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •