Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    20

    Unanswered: compare form to table

    i'm having trouble comparing a form text box to the table to make sure the information entered was valid and should show a result. what i'm trying to do is have a form that has a text box that takes in a machine number and the user can open up certain information about the machine. The trouble i'm having is that if they enter in a machine number that is not there, i want a message box to pop up and tell them that no machine was found in the database. i haven't the slightest clue on how to write it. i've tried a bunch of different ways but to no avail. i have an error check if they don't enter a machine at all, though. This is my code for one of my buttons.

    Private Sub cmdOpenPeriph_Click()
    On Error GoTo Err_cmdOpenPeriph_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim x As String


    stLinkCriteria = "[MACH#]=" & "'" & Me![txtMACH#] & "'"

    If IsNull(Me![txtMACH#]) Then
    x = MsgBox("Please enter a Machine Number to look up.", vbOKOnly)
    Else
    stDocName = "PeripheralTable"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If


    Exit_cmdOpenPeriph_Click:
    Exit Sub

    Err_cmdOpenPeriph_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenPeriph_Click

    End Sub

    Any help would be greatly appreciated. Have a good day.

  2. #2
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    You may want to use a recordset to do this.
    Like this:

    ---------
    Dim RST as recordset

    set RST=new recordset
    RST.open "Select * from [YourTable] where machine='" & me!TXTmach & "'", currentproject.connection, adopenstatic

    ----------

    and then use a recordcount to see if its 0 or if its higher.
    using:

    ----
    if recordset=0 then
    x = MsgBox("Please enter a Machine Number to look up.", vbOKOnly)
    else
    ....
    end if
    ------

  3. #3
    Join Date
    May 2004
    Posts
    20
    after trying that, i get the error "object variable or with block variable not set". i'm not 100% sure what that means but here is my new revised code.

    Private Sub cmdOpenPeriph_Click()
    On Error GoTo Err_cmdOpenPeriph_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim x As String
    Dim rst As Recordset
    Set rst = New Recordset

    rst.Open "Select * from [ComputerInformation] where [MACH#]='" & Me![txtMach#] & "'", CurrentProject.Connection, adOpenStatic
    stLinkCriteria = "[MACH#]=" & "'" & Me![txtMach#] & "'"

    If IsNull(Me![txtMach#]) Then
    x = MsgBox("Please enter a Machine Number to look up.", vbOKOnly)
    ElseIf Recordset = 0 Then
    x = MsgBox("Please Enter a Valid Machine Identity", vbOKOnly)
    Else
    stDocName = "PeripheralTable"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If



    Exit_cmdOpenPeriph_Click:
    Exit Sub

    Err_cmdOpenPeriph_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenPeriph_Click

    End Sub

  4. #4
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    Are you using ADO(ActiveX Data Object) or DAO objects. You can see this in the tools=>references.

  5. #5
    Join Date
    May 2004
    Posts
    20
    i believe that i'm using ado. i don't have a tools ==> references. but under tools it does have an active x controls option. this is using the xp edition of access also if that is of any help. Thanks

  6. #6
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    So on which part of the code are you getting the error on?

  7. #7
    Join Date
    May 2004
    Posts
    20
    it doesn't give me the lines of code that i am having the issue with. it has to be either on the line

    rst.Open "Select * from [ComputerInformation] where [MACH#]='" & Me![txtMach#] & "'", CurrentProject.Connection, adOpenStatic

    or

    ElseIf Recordset = 0 Then

    i'm not sure what is causing this error.

  8. #8
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    I have a few questions

    Whats the field name in the table and is it a text or an number?
    Cause the SQL statement would be different for string or integer.

    and

    Whats the control name that it should check with?
    Cause im not sure if they are both controls or one is a field and the other is a control.

  9. #9
    Join Date
    May 2004
    Posts
    20
    the field name in the table would be "MACH#" and it is a text field. I need to compare this field with a text box field on a form that is unbound to make sure that what they entered actually exists in the table. the table name is just "ComputerInformation" where this field lies.

Posting Permissions

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