Access 2000 query message box

  Carpigiani 10:18 05 Dec 2003

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?

  Big Elf 11:34 05 Dec 2003

I don't know of a way do do that but why not have them choose a county from a choice list that includes them all.

  Big Elf 11:36 05 Dec 2003

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.

  Carpigiani 20:27 05 Dec 2003

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.

  Carpigiani 23:57 07 Dec 2003

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.

  skeletal 12:50 08 Dec 2003

Try this:

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
Option Explicit
Dim CountryName As String, String1 As String
Dim stDocName As String
Dim rec
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
End If

qdf.Close 'Close the query

DoCmd.DeleteObject acQuery, stDocName 'Delete the query
RefreshDatabaseWindow 'Update the database window

'Error trapping
Exit Sub

MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

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.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone