Replies

The solution you suggested worked very well. Thanks. Well, I have to be sure that what showed well while in debugging session worked the same while out of debug mode.

Thanks for your prompt and thorough reply. I use method(1) to catch the error. Method (2) didn’t work as it didn’t catch the error – it didn’t catch error due to null value for a required field. Method(3) works for my combo boxes.

Thanks for your explanation, HansV. My tables contains only about 1000 rows and it is rather small. I experimnted with it and couldn’t visibly find the difference. That’s why I seek help. Thanks again, and to Charlotte too.

Sorry for the errors in the Sub that I sent. It is mainly due to my shoddy attempt to edit out irrelevant portion of the codes and renaming some of the variables. I re-post my problem as follows:

I have a main form frmMain which displays all the records in my main table (the RecordSourec). I have a command button in frmMain called cmdSearch which would open up a form frmSearch where users key in search data (one of them is txtName for the field Name) for the various fields to search for the desired records. When a user press the command button cmdAccept in frmSearch, frmSearch would be closed and the searched results would be displayed in frmMain.

The Sub Accept for cmdAccept is as follows (both the detailed SQL statement (to search for other fields) and the error trapping are left out for simplicity):

Private Sub Accept()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = “SELECT * FROM tblData WHERE Name LIKE ‘” & txtName & “*'”
strSQL = strSQL & ” ORDER BY Name;”

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
MsgBox “There is no record.”
rst.Close
Set rst = Nothing
DoCmd.Close
Exit Sub
End If

‘ Populate the recordset else only the first record will be displayed
rst.MoveLast
rst.MoveFirst
DoCmd.Close
Set Forms(“frmMain”).Recordset = rst
‘ The rst isn’t closed
Set rst = Nothing
Set db = Nothing

End Sub

The codes work except that I need to insert
rst.MoveLast
rst.MoveFirst
before I close the form.

I followed your advice as I understand it and re-code the Sub as below (let me call it Accept1:

Private Sub Accept1()

Dim strSQL As String

strSQL = “SELECT * FROM tblData WHERE Name LIKE ‘” & txtName & “*'”
‘ Close frmSearch
DoCmd.Close
Forms(“frmMain”).RecordSource = strSQL

End Sub

When I ran the program, my frmMain showed only a single empty record. Did I understand your correctly, Charlotte? Where did I go wrong?

Here is the codes, Charlotte:

Private Sub cmdOK()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

On Error GoTo TanganiErr

‘ txtName is a textbox for user’s input in frmInfo
strSQL = “SELECT * FROM tblData WHERE Name LIKE ‘” & txtName & “*'”
End If

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.MoveFirst

‘ frmInfo is the calling form in which txtName is found
Set Forms(“frmInfo”).Recordset = rst
Set rst = Nothing
Set db = Nothing

End Sub

Thanks Gray & Dave for your help.

I did a debug.print to list out the names after executing the SQL command, all the names starting with “L” were printed, implying the SQL statement was okay. After debug,print, the form displays correctly too all the records required. If I comment out the debug. print statement, it was back to single record.

Then I added rst.MoveLast and rst.MoveFirst after running the SQL, the form displays correctly all records with names that start with “L”.

The reason(s), anyone?

When I read your reply, Charlotte, it immedately hit me that you had gotten to the root of the problem! My Print command button prints the current record (record on display), and I used SQL to narrow it down to a single record before sending it to the report. I re-issued the SQL after I hit the Print button, and everything is back to normal. Thanks a lot, Charlotte. Why it had never dawned on me that I had filtered the table down to a single record?

BTW, is there a simple method with DoCmd that I can use to print the current record without resorting to SQL?

I was a little hesitant to send my sub over because all my user’s variable anmes, sub names, messages etc were written using the local native language, or a combination of English and the native language, and they may appear odd to you without editing. I will send one over next time if there is a necessity. Thanks.

Yes, I can compact my database using the Access’s Database Utilities, but what if I want to do the same in codes within the database, say as an option in the user’s switchboard or user’s menu, without the benefit of the Database Utilities in the Access’s menu?

I am under the impression that autonumber once given, is given, even if a record with that autonumber is removed, leaving gap in the autonumbering. I do use autonumber in my database. If I have a 100 records with the same sequential autonumber, and I removed record number 50 (also autonumber 50), the autonumber 50 will not be issued again. Or does it affect only the highest autonumber, meaning the 100th reord? If I remove record with autonumber 100, compact the database, then the first new record wii have autonumber 100. Is it what you said?

If I use autonumber as my row ID, but keep the records marked with deletion in the same table as the main one, then there will not be any problem?

The other command buttons on the form are navigation buttons, Search, and Close buttons (containing plain DoCmd.Close). They failed to respond after printing but were responding as expected if the Print button wasn’t executed. I can display my listbox and combobox on the form, but I can’t update the fields. I can’t change the values in my textboxes. Have I done anything wrong?

Thanks, Charlotte. I am a long-time user of MS Windows but just beginning to program in it. I am glad I shift to writing database program in Windows and changing my habit and mindset which was orientated to DOS platform along the way. I know more about intricacies about Windows programming. Another reason for me to move to Windows platform has to do with printers – more and more new printers provide no drivers for DOS printing!

Sorry, MarkD, neither do I know who is David:) I got mixed up along the way. My sincere apology and thanks for your help.

Thanks, David. It works.

No, Charlotte, the items in the two listboxes are independent of each other, though they are under the same broad category. DavidD’s advice worked very well for me. Thanks.

Thanks Charlotte, your solution works like a charm.