Search by autonumber id

[thart21]

I have the following code attached to a submit button on my form with just one unbound field "projectid". I need to add an error message when the projectid entered does not exist in the database. What I have tried is not working so far and would appreciate some help with the code.
Private Sub Command3_Click()

If Not IsNull(Me.projectid) Then
strWhere = " (tblProjectDetails.projectid) = " & Me.projectid
End If

---section not working----
If Me.projectid <> (tblProjectDetails.projectid) Then
MsgBox "There is no project in the database with the ID number entered.", vbExclamation, "Error"
Cancel = True
------End If ------

DoCmd.OpenForm "Projects", , , strWhere


End Sub

Thanks!

 

[KenHigg]

I think you need to try and work in a domain function in there somewhere. Lookup dlookup() and see if it'll work...


ken

 

[boblarson]

Lookup dlookup() and see if it'll work...

An MVP corrected me on that one too. You should probably use

DCOUNT as it will not return an error if it can't find it where DLOOKUP will. You can just use

If DCount("[yourfieldnamehere]","yourtablenamehere","[IDFieldName]=" & Me!IDField)>0 Then
Msgbox "Item exists"
End If

etc.

 

[KenHigg]

I hadn't thought of that -


ken

 

[boblarson]

I hadn't thought of that -


ken

Yeah, don't feel bad - I didn't either and then an MVP corrected me on that one. So, I was just passing along the knowledge. It's all a learning experience, eh?

 

[thart21]

Thanks for the quick responses. Is the DCount going to take care of the user entering an incorrect projectid#? It looks like it's just going to look for a number in the field (>0) but perhaps I am reading it incorrectly.

Thanks!

 

[KenHigg]

And it would have reared it's ugly head at the most inopportune time

 

[boblarson]

Thanks for the quick responses. Is the DCount going to take care of the user entering an incorrect projectid#? It looks like it's just going to look for a number in the field (>0) but perhaps I am reading it incorrectly.

Thanks!

The >0 part is because DCount returns a COUNT (hence number) of how many times that item is in the domain you are checking.

However, as Smokey the Bear says, "Only YOU can prevent forest fires," and that goes with this too. You can try to come up with an algorithm to keep someone from entering something wrong, but it can be a pain in the butt. So, if you want them to be limited to ONLY those existing items, use a combo box or list box to display to them existing items.

 

[rainman89]

Thanks for the quick responses. Is the DCount going to take care of the user entering an incorrect projectid#? It looks like it's just going to look for a number in the field (>0) but perhaps I am reading it incorrectly.

Thanks!

you are. what it does is count how many instances where that criteria is met. and make sure that is greater than zero, which means it already exists

 

[KenHigg]

I may be missing something (again ), but the code would be something like;

If dcount(xxx) = 0 Then
  msgbox "Record Not Found...")
  Exit Sub
End if

???
ken

 

[thart21]

Thanks so much, works perfectly!!!

Private Sub Command3_Click()

If DCount("[projectid]", "tblProjectDetails", "[projectid]=" & Me!projectid) > 0 Then

strWhere = " (tblProjectDetails.projectid) = " & Me.projectid
DoCmd.OpenForm "Projects", , , strWhere
Else
MsgBox "Item doesn't exist"
End If

End Sub

 

[boblarson]

Glad to hear you got it working.