Checking a record in a table

[widstje]

I was hoping someone could help me here.



I've got a form that a user enters a map number into. I want to check a master list of map numbers (stored in a table) to see if it exists. I'd like something of a true/false answer so I can then direct the user to the next appropriate form. Right now I just send the user to a form and then refresh the list boxes on the form based on the number provided. If the map number is new or wrong it just shows an empty form. I'd rather have something that checks the table ahead of time and pops up a message box if no record exists. Here is my current code:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim frm As Form, txt As TextBox
Dim frm2 As Form, txt2 As TextBox, txt3 As TextBox

stDocName = "search_records"
stLinkCriteria = "[MAP_NUMB]='" & Me.strMap & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set frm = Forms![search_records]
Set txt = Forms![search_records]!Text1
frm.RefreshLists
frm.SetFocus
txt.SetFocus
End If

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.description
Resume Exit_Command0_Click

[jya]

Would adding what I put in bold help? If the recordset is empty, close the form and give a message that there was no match.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim frm As Form, txt As TextBox
Dim frm2 As Form, txt2 As TextBox, txt3 As TextBox

stDocName = "search_records"
stLinkCriteria = "[MAP_NUMB]='" & Me.strMap & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If Forms(stDocName).Recordset.EOF then
DoCmd.Close acform, stDocName
msgbox "No map was found"
Else

Set frm = Forms![search_records]
Set txt = Forms![search_records]!Text1
frm.RefreshLists
frm.SetFocus
txt.SetFocus
End If

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.description
Resume Exit_Command0_Click

[widstje]

Thank you. That works good. I think I'd rather not have the other screen flash open and then close though.

Anyone else have any ideas?

[jya]

Hadn't used this code recently, but it will run a query instead of opening a form and see if there is a match.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from search_records where [Map_numb] = '" & Me!strMap & "'"

rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSQL

If rs.EOF Then
MsgBox "no record was found"
Else


To All,

Another place to go for Access help would be

http://www.access-programmers.co.uk/forums/

[widstje]

thanks for the help. that works perfectly.