Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Trying to retrieve a record from a table and assign data to text fields in a form

    I have a form with a combo box field that shows the log no's from a table. I want to be able to select a log no press a "find" command button and display the data in the text fields in the form. The following code is what I am running when the Find button is pressed:


    Dim db As Database
    Dim rstESRTable As Recordset

    Set db = CurrentDb()
    Set rstESRTable = db.OpenRecordset("SELECT * FROM ESR_Table WHERE [ESR_Table.Log_No] = " & cboLogNo & "", dbOpenDynaset)

    If rstESRTable.NoMatch Then
    MsgBox cboLogNo & " Not found"
    End If

    Me!txtPlant = rstESRTable("Plant")
    Me!frmSampleArea = rstESRTable("Sample_Area")
    Me!txtLogNo = rstESRTable("Log_No")
    Me!txtPartNo = rstESRTable("part_no")
    Me!txtRev = rstESRTable("Rev")
    etc.......

    When it executes, it stops on Me!txtPlant = rstESRTable("Plant") and gives me a run-time error 2113 "The value you entered isn't valid for this field".

    What is the correct way to display the data in a text field?????

    Thanks,

    SBR

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    I do not know which version of Access you are using but the noMatch is usually used with a find or seek command and not an openrecordset command. Try using a record count of 0 to prompt user of record not found.

    If this does not clear things up then look to see if there is some validation in the text box control and check the format in the control to see if it is compatible with the datatype you are bringing in.
    KC

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by AZ KC
    Try using a record count of 0 to prompt user of record not found.
    I agree with him. Try something like this:

    Code:
    If rstESRTable.RecordCount = 0 Then
       MsgBox cboLogNo & " Not found"
    Else
       Me!txtPlant = rstESRTable("Plant")
       Me!frmSampleArea = rstESRTable("Sample_Area")
       Me!txtLogNo = rstESRTable("Log_No")
       Me!txtPartNo = rstESRTable("part_no")
       Me!txtRev = rstESRTable("Rev")
       etc.......
    End If
    Oh, and to address why you were getting that error:

    You closed the if statement after the MsgBox call and allowed the sub to continue executing as opposed to placing the "me!control =" into the "Else" branch of the IF statement. Basically it thought you were trying to assign null for the value, which it doesn't much care for.

    Easy Fix
    Last edited by Teddy; 03-09-04 at 19:50.

  4. #4
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    I remarked out the If statement to make sure it wasn't causing the problem and I still get the messge. It doesn't like the statements that are supposed to be replacing the text fields with the data from the record. All of the fields on the form are text fields and I have no validation set for the fields.

    Thanks,

    SBR

  5. #5
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Thanks, I changed it to

    If rstESRTable.RecordCount = 0 Then
    MsgBox cboLogNo & " Not found"
    Else
    Me!txtPlant = rstESRTable("Plant")
    Me!frmSampleArea = rstESRTable("Sample_Area")
    Me!txtLogNo = rstESRTable("Log_No")
    Me!txtPartNo = rstESRTable("part_no")
    Me!txtRev = rstESRTable("Rev")
    etc.......
    End If

    The run-time error is gone (thanks), but it doesn't find any of the log numbers I choose. I always get the message when record count = 0. Do you see anything wrong with my SQL statement?

    Thanks,

    Sherry

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by sbr7770
    I remarked out the If statement to make sure it wasn't causing the problem and I still get the messge. It doesn't like the statements that are supposed to be replacing the text fields with the data from the record. All of the fields on the form are text fields and I have no validation set for the fields.

    Thanks,

    SBR
    You NEED the execution to break at that if statement. If you allow the code to attempt to set your txtbox values to null, it's going to bomb. Let me see if I can explain a bit better...

    Code:
    Dim db As Database
    Dim rstESRTable As Recordset
    
    Set db = CurrentDb()
    Set rstESRTable = db.OpenRecordset("SELECT * FROM ESR_Table WHERE [ESR_Table.Log_No] = " & cboLogNo & "", dbOpenDynaset)
    Ok, all is well up to here. Now, knowing that the issue at hand is assigning bad values to the txtboxes because there are no records, we need error checking. Right now you have this:

    Code:
    If rstESRTable.NoMatch Then
    MsgBox cboLogNo & " Not found"
    End If
    That fails because it doesn't provide any error handling. If the statement returns true, a message box pops up, the user presses ok, and the code continues to execute. This is bad, because now you have null values about to be inserted into your controls.

    So, you need to alter the statement to NOT execute the frm!control = portion. Like so:
    Code:
    If rstESRTable.RecordCount = 0 Then
       MsgBox cboLogNo & " Not found"
    Else 'This is where you're getting screwed up
       Me!txtPlant = rstESRTable("Plant")
       Me!frmSampleArea = rstESRTable("Sample_Area")
       Me!txtLogNo = rstESRTable("Log_No")
       Me!txtPartNo = rstESRTable("part_no")
       Me!txtRev = rstESRTable("Rev")
       etc.......
    End If
    All that stuff MUST go in the "Else" portion of the statement or it's going to bomb. If you want them set to what looks like null, set them to empty strings in the If portion like so:
    [code]
    Code:
    If rstESRTable.RecordCount = 0 Then
       MsgBox cboLogNo & " Not found"
       Me!txtPlant = ""
       Me!frmSampleArea = ""
       Me!txtLogNo = ""
       etc.
    Else 'This is where you're getting screwed up
       Me!txtPlant = rstESRTable("Plant")
       Me!frmSampleArea = rstESRTable("Sample_Area")
       Me!txtLogNo = rstESRTable("Log_No")
       Me!txtPartNo = rstESRTable("part_no")
       Me!txtRev = rstESRTable("Rev")
       etc.......
    End If
    Does this make sense?

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by sbr7770
    Thanks, I changed it to

    If rstESRTable.RecordCount = 0 Then
    MsgBox cboLogNo & " Not found"
    Else
    Me!txtPlant = rstESRTable("Plant")
    Me!frmSampleArea = rstESRTable("Sample_Area")
    Me!txtLogNo = rstESRTable("Log_No")
    Me!txtPartNo = rstESRTable("part_no")
    Me!txtRev = rstESRTable("Rev")
    etc.......
    End If

    The run-time error is gone (thanks), but it doesn't find any of the log numbers I choose. I always get the message when record count = 0. Do you see anything wrong with my SQL statement?

    Thanks,


    Sherry
    Oh man... I just typed all that out for nothing?

    *sob*

    Your problem now is that there are never any records in the recordset. That's because you're including an extra set of quotes. It's looking for:

    WHERE [numberfield] = 5"

    Try this:

    Set rstESRTable = db.OpenRecordset("SELECT * FROM ESR_Table WHERE [ESR_Table.Log_No] = " & cboLogNo, dbOpenDynaset)

  8. #8
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Yes, it does make sense. Thanks. I changed it as you suggested but there is still something wrong somewhere. For every log no I select in the combo box, I get the message box "logno not found".

    Thanks for your help!

    SBR

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by sbr7770
    Yes, it does make sense. Thanks. I changed it as you suggested but there is still something wrong somewhere. For every log no I select in the combo box, I get the message box "logno not found".

    Thanks for your help!

    SBR
    Hmmm, well, you're not properly populating the recordset. My first hunch is the SQL statement. So step 1 needs to be making sure the SQL statement you're setting for the recordset is good.

    Try creating a new button, call it debug or whatever, then put this in the OnClick event:

    MsgBox "SELECT * FROM ESR_Table WHERE [ESR_Table.Log_No] = " & cboLogNo

    Then I would put the string into the SQL source of a brand new query and make sure you are indeed returning records.

    I'm off for the evening, but that should narrow it down pretty good.

  10. #10
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    I made the change and it still doesn't work. I have been working on this all day and it has been very frustrating!

    Thanks for your help!

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WAIT, I SEE IT!

    Hehe.

    "SELECT * FROM ESR_Table WHERE [ESR_Table].[Log_No] = " & cboLogNo

    ...damn brackets
    Last edited by Teddy; 03-09-04 at 20:24.

  12. #12
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    I'll try those ideas and pray that it works, thanks again. Have a good evening!

    SBR

  13. #13
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Okay, this is what I have so far:

    I tried adding the brackets, removing the brackets every possible way, but I could not get the Where clause to find the logno.

    I took the Where clause and now it finds the first record, but on the form the only field that displays data is the log no. Nothing shows up in the rest of the fields. Any ideas what is wrong now????

    Dim db As Database
    Dim rst As Recordset
    Dim sSQL As String
    Set db = CurrentDb()

    Set rst = db.OpenRecordset("SELECT * FROM ESR_Table", dbOpenDynaset)

    If rst.RecordCount = 0 Then
    MsgBox cboLogNo & " Not found"
    Else
    Me.txtPlant = rst.Fields("Plant")
    Me!frmSampleArea = rst.Fields("Sample_Area")
    Me!txtLogNo = rst.Fields("Log_No")
    Me!txtPartNo = rst.Fields("part_no")
    Me!txtRev = rst.Fields("Rev")
    Me!txtQtyReq = rst.Fields("Qty_Required")
    Me!txtDateReq = rst.Fields("Date_Required")
    Me!txtProjectNo = rst.Fields("Project_No")

    Thanks,

    SBR

  14. #14
    Join Date
    Mar 2004
    Posts
    21
    I'm trying to apply this same process to a command button. I want to return a record field value to a form field. But when I run the command I get the error "Compile Error: User-defined type not defined" and debugging highlights the 'db as Database' line.

    Here is the code I'm using:
    Private Sub Search_Command_Click()
    Dim db As Database
    Dim rst As Recordset

    MsgBox "CustomerNbr: " & CustomerNbr, vbOKOnly, "Value Notice"

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT CUSTOMER_SEARCH FROM Customers WHERE [Customers].[Customer]='" & CustomerNbr & "'", dbOpenDynaset)

    Me!txtCustomerName = rst.Fields("CUSTOMER_SEARCH")
    MsgBox "CustomerName: " & CustomerName, vbOKOnly, "Value Notice"

    Forms!Order_Entry!CustomerName.Visible = True

    Close rst

    SendKeys "{TAB}"

    End Sub
    ---------------------
    Thanks
    David

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by mrbaffl
    I'm trying to apply this same process to a command button. I want to return a record field value to a form field. But when I run the command I get the error "Compile Error: User-defined type not defined" and debugging highlights the 'db as Database' line.

    Here is the code I'm using:
    Private Sub Search_Command_Click()
    Dim db As Database
    Dim rst As Recordset

    MsgBox "CustomerNbr: " & CustomerNbr, vbOKOnly, "Value Notice"

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT CUSTOMER_SEARCH FROM Customers WHERE [Customers].[Customer]='" & CustomerNbr & "'", dbOpenDynaset)

    Me!txtCustomerName = rst.Fields("CUSTOMER_SEARCH")
    MsgBox "CustomerName: " & CustomerName, vbOKOnly, "Value Notice"

    Forms!Order_Entry!CustomerName.Visible = True

    Close rst

    SendKeys "{TAB}"

    End Sub
    You gotta reference the MS DAO 3.6 library in the Tools->References section ...

Posting Permissions

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