Check if record exists in a table using vba 1

[frontside]

Hello

I have a form that´s not based on a query or table, on this form I have a field that I use to display a record in a subform.

I need to check if I get a match before I use the subform query. if record exist messagebox("record exists") and if it does not get a match msgbox("record does not exist")

I´m all new to finding a record in a recordset so it would be nice to get a newby answer
I tried to use some of the code in other posts but couldnt get it to work.

Form: Huvudmeny
FormField for check: TxtOrdernr
Table: TOrder
Field in table: Ordernr


Let me know if you need any more information


//Micke

 

[PHV]

You may consider the DCount or DLookUp functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frontside]

I know I can use Dlookup to show a record but how do I/Can I use it to display if the record exist?

 

[PHV]

A starting point:
If IsNull(DLookUp(...)) Then
MsgBox "record does not exist"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frontside]

it works fine if a match exist but when it doesnt I get a error message, this is the code I was using.

If IsNull(DLookup("[Tillv_ordernr]", "MyTable", "[Tillv_ordernr] =" _
& Forms![huvudmeny]![TxtTONR])) Then
MsgBox ("does not exist")
Else
MsgBox ("Exist")
End If


any ideas?

 

[PHV]

I get a error message
Any chance you could post some relevant info like, say, the error message and which line is highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frontside]

Sorry,

the following part is highlighted (only when I dont get a match)
If IsNull(DLookup("[Tillv_ordernr]", "1 Rapport Etikett urval", "[Tillv_ordernr] =" _
& Forms![huvudmeny]![TxtTONR])) Then

the errormessage is in Swedish but It translates something like this:
error nr 3464
typemissmatch in term expression
(sorry for the bad translation, I get a feeling that the missmatch is when you compare a field with the null result from the dlookup),

let me know if you need to know anything else

 

[PHV]

You may try this:
If IsNull(DLookup("Tillv_ordernr", "[1 Rapport Etikett urval]", "Tillv_ordernr=" _
& Nz(Forms!huvudmeny!TxtTONR, 0))) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frontside]

Thanks soo much for helping me but I still get the same message, isnt there a way of just disable the errormessages in vba? that would solve my problem, then I just put a messagebox in the error section of my button/field.

Any ideas?

 

[PHV]

What is the data type of Tillv_ordernr in the [1 Rapport Etikett urval] table ?
What is the value of Forms!huvudmeny!TxtTONR when the error is raised ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frontside]

I mananged to get it to work when I changed the format on the table, another problem is that I´m testing on a database where I have imported the whole table from the main system but on the "real" database I´m going to have linked tables. And with linked tables theres no way for me to change the data type?

the data type was text from the start and then I changed it to numbers, when I did the conversion I got a message saying that I cant convert a null value. then I got the code to work,
I then converted the table back to text and the code still worked!?! Strange


Thanks for all the help

//Micke

 

[PHV]

for a text datatype:[tt]
If IsNull(DLookup("Tillv_ordernr", "[1 Rapport Etikett urval]", "Tillv_ordernr='" _
& Forms!huvudmeny!TxtTONR & "'")) Then[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886