Honor 9 Lite review
I am creating a database in MS Access 2000.
I have a table holding customer information, including which county they are from.
I have created a query with a message box saying "Please Enter County." This works fine, it brings up all the records with the county that is typed in.
I would like a message box to open and tell the user "No records found" if they type a county into the message box which asks them to input the county.
Anyone have any ideas on how to do this in Access 2000? Any ideas on how to do it using SQL or VBA?
I should have said that I don't know of a way to do that because I have limited knowledge of Access rather than imply it's not possible.
Your idea is OK in theory, but the query is to show the records, if there aren't any records i need it to tell the user this.
Still got the problem, wondered if there is anyone else around now that might be able to help?
My computing teachers AND the college technicians have so far failed to find a solution, and they have been running A level MS Access projects for years. Oh well, here's hoping.
Create a table called table1 and set up three fields called ID, Name and Country. Make a form called Form1 and put a button on it. Keep the name of the button "Command1".
Put this code in, using the VBA editor (right click your new button in design view and chose build event) but make sure you end up with only 1 "Sub_command1_Click as Access will generate one automatically for you:
Option Compare Database
Dim CountryName As String, String1 As String
Dim stDocName As String
Dim num As Integer
Dim qdf As QueryDef
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
stDocName = "result" 'Use this name as the query name
Set qdf = CurrentDb.CreateQueryDef(stDocName) 'Create a query object called stDocName
CountryName = InputBox("Enter country name", "My input") 'Get the name of a country
'Generate a SQL command assuming there is a table called "Table1" with three records
'one called "ID", one "Name" and the other "Country". Chr$34 is a method of putting the
'character " into a string. CountryName is the variable taken from the inputbox above.
'You must leave a space at the end of each line inside the "" e.g. after Table1.Country ".
'This SQL command can be generated from a "normal" Access query by going into design view and
'right clicking at the top of the form on SQL view.
String1 = "SELECT Table1.ID, Table1.Name, Table1.Country " & _
"FROM Table1 " & _
"WHERE (((Table1.Country) Like" & Chr$(34) & CountryName & Chr$(34) & "));"
qdf.SQL = (String1) ' Run the query using the SQL statement String1
Set rec = CurrentDb.OpenRecordset(String1, dbOpenSnapshot) 'Generate a recordset from the new query
If rec.RecordCount Then ' If there are some records in the new query
rec.MoveLast 'Move to the end
rec.MoveFirst 'And back to the start to make sure all are counted
num = rec.RecordCount 'How many are there
MsgBox "There are " & num & " records of that name" 'Message box saying how many there are
MsgBox "There are no records including that name" 'But if there are none, say so
qdf.Close 'Close the query
DoCmd.DeleteObject acQuery, stDocName 'Delete the query
RefreshDatabaseWindow 'Update the database window
The format is likely to get screwed. If so, send me an email and I can send you the code direct.
This thread is now locked and can not be replied to.