Unanswered: Access 2003- error when searching for record from command button
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.
Set rs = MSODSC.DataPages(0).Recordset.Clone
' 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"
' Check search results for success.
If (rs.bof) or (rs.eof) Then
Msgbox "No Project found",,"Search Done"
MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
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
As PMOProjectID is a numeric data in the recordset, the .Find line should be:
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.
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).
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
Indexed: Yes (No Duplicates)
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.