Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Access 2003- error when searching for record from command button

    Hi Everyone,
    I'm very new at coding, so I am hoping someone can help.
    I want to create a command button that people can click on that will ask for an ID number which is also the primary key of the access database - I have picked up this script from microsoft, but now receive an error when I hit ok in the pop up window (error is Empty row cannot be inserted. Row must have at least one column value set.)
    Script is as follows- can anyone please help me with this? (and explain it simply???) thanks


    <SCRIPT language=vbscript event=onclick for=Command1>
    <!--
    ' Clone the recordset.
    Dim rs
    Set rs = MSODSC.DataPages(0).Recordset.Clone
    Onerrorresumenext
    ' This line assumes that the value you are filtering on is an integer.
    ' If the search value is a string, use slightly different syntax.
    ' For example, "PMOProjectID = '" & CStr(InputBox("Please enter PMO ProjectID to find", "Find")) & "'"
    ' rs.find "PMOProjectID=" & cLng(inputbox("Enter a PMO ProjectID","Find"))
    rs.find "PMOProjectID = '" & CStr(InputBox("Please enter project ID to find", "Find")) & "'"
    ' Custom error handling.
    If (err.number <> 0) Then
    Msgbox "Error: " & err.number & " " & err.description,,"Invalid Search"
    ExitSub
    EndIf
    ' Check search results for success.
    If (rs.bof) or (rs.eof) Then
    Msgbox "No Project found",,"Search Done"
    ExitSub
    EndIf
    MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
    -->
    </SCRIPT>

  2. #2
    Join Date
    Jul 2009
    Posts
    4
    The other option I was thinking of is having a text box on the page with a command button - I want people to enter their ID number and then the record be retreived from the database and edited with additional info.

    I have created a text box & button combo, which doesn't error, but it's not working either.
    Code is (also curteousy of MS):

    '-----------------------------------------------------------------------
    'This routine searches all fields in the defaultrecordset for something
    'entered by a user in a Search text box. It passes through each field
    'the recordset until it finds a match.
    '-----------------------------------------------------------------------
    dim i 'Counter variable
    dim rs 'ADO recordset object
    dim fld 'ADO field object
    dim FieldCount 'Number of fields in the recordset

    FieldCount = MSODSC.DefaultRecordset.Fields.Count

    'This will return the default recordset on the page
    'in this case, the Customers table.
    set rs = MSODSC.DefaultRecordset

    for i = 0 to FieldCount - 1
    'get a field object
    set fld = rs.Fields(i)

    '0 = Skip no records
    '1 = Search forward
    '1 = Start with the first record
    rs.Find fld.name & " = '" & txtSearch.value & "'", 0, 1, 1

    'Check for EOF. If at EOF but have not exhausted
    'all the fields, then reset to the first position in the
    'recordset. Otherwise, if a match was found, exit the loop.
    if rs.EOF then
    rs.MoveFirst
    else
    exit for
    end if
    next

    'Clean up.
    set fld = nothing
    set rs = nothing

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As PMOProjectID is a numeric data in the recordset, the .Find line should be:

    Code:
    s.Find "PMOProjectID = " & CLng(InputBox("Please enter project ID to find", "Find"))
    Also, it would be better to use a variable to capture the value returned by the InputBox and test it before using it in the find method: What if the InputBox returns an empty string, what if the user does not type a number in the box etc. ?

    Your second method is not an efficient one and would become very slow, should the number of records be high.

    Have a nice day!

  4. #4
    Join Date
    Jul 2009
    Posts
    4
    Ok, so I have made the amendment.

    Instead of the Empty row cannot be inserted. Row must have at least one column value set error, I am now getting Error: 13 Type Mismatch.

    The format of the primary key field is PI###; and this is how I want people to search.

    If I only enter 3 numbers (eg 045) I get the Empty row cannot be inserted. Row must have at least one column value set error.

    What needs to be added to make the search the same as the format of the field?

    I will attempt to google too, but figure since you are all experts, someone might just know the answer and be able to help out

    Thanks in advance.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In your original post you were talking about an ID number:
    ... that people can click on that will ask for an ID number which is also the primary key...
    Now you write that the primary key is in the format PI###; which is obviously not a number. Did I miss something ? If you are seeking in a Text column, your original code was correct as far as the casting of value returned by the inputbox is concerned (Cstr, and not CLng as I wrote).

    What I don't understand either is why you're talking about inserting a row, while you're only using the Find method of the recordset (by the way, you should work with the Filter property of the recordset, it could be more effective and probably quicker).

    Which line of codes produces the error exactly?

    Have a nice day!

  6. #6
    Join Date
    Jul 2009
    Posts
    4
    Ok, let me try to explain this better as I need all the help I can get with this one!

    First, I'm not talking about inserting a row - I am getting an error saying "Empty row cannot be inserted. Row must have at least one column value set" when I press the command button, the window pops up (as I would like it to) and then when I type in the record ID (without the prefix of "PI") it returns that error.

    Secondly, the field I am trying to read from IS the primary key, it is set to auto number & increment, but appear as formatted PI###.

    The field settings for the primary key are:

    Field Size: Long Integer
    New Values: Increment
    Format: "PI"000;#;_
    Caption:
    Indexed: Yes (No Duplicates)
    Smart Tags:

    If I click the button and type in "PI045" and click ok, I get the Error: 13 Type Mismatch.

    Finally what I am trying to do is:

    1) Type in the Record number in the format PI###
    2) Retrieve the record for the value typed in (eg, return record PI045 if the search was on PI045)
    3) Have the values that exist for the record prepopulate into the data access page, and have them editable; and finally
    4) Have an "update record" command button to make amendments to the record.

    I hope this provides more clarity.
    Thank you again.

Posting Permissions

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