Help with DLookup

[miss031]

I'm sure it's been covered here, but I couldn't find an exact explanation
that covers my problem:

I think I need DLookup to find out if a record exists in my table. I would
like to find out if [bidder_ID] exists for the [sale_date_ID] in the table
[tbl_cont_bidder]. If there is already a bidder_id for the given
sale_date_ID, then I would like to throw a message. If there is not, I would
run a query. I just don't get the DLookup part.

 

[miss031]

Unless I could use an index on the table instead, and just throw an error
message if it is violated?

 

[Maurice]

If it's just one record you are trying to add with a query I would go for the
general index idea and let Access handle it. Your appendquery will indeed not
add the record if the key is violated so in that case you could use the
append (run it, it won't be added anyway). With a dlookup you do make an
extra roundtrip.

dlookup("field to look for", "table where to look", "must meet this criteria")

hth

 

[miss031]

That's kinda what I figured, and I added an index to the table that includes
both field, but how do I get a custom msgbox instead of the index violation
error?

If it's just one record you are trying to add with a query I would go for the
general index idea and let Access handle it. Your appendquery will indeed not
add the record if the key is violated so in that case you could use the
append (run it, it won't be added anyway). With a dlookup you do make an
extra roundtrip.

dlookup("field to look for", "table where to look", "must meet this criteria")

hth
--
Maurice Ausum

I'm sure it's been covered here, but I couldn't find an exact explanation
that covers my problem:

I think I need DLookup to find out if a record exists in my table. I would
like to find out if [bidder_ID] exists for the [sale_date_ID] in the table
[tbl_cont_bidder]. If there is already a bidder_id for the given
sale_date_ID, then I would like to throw a message. If there is not, I would
run a query. I just don't get the DLookup part.

 

[Maurice]

Try the recordsaffected property which you can use to see if any records were
added or not. Look in the help for a asmple of this. Based on the outcome you
can show your own messagebox.

instead of using the docmd.openquery use the execute method
(currentdb.execute)
--
Maurice Ausum

That's kinda what I figured, and I added an index to the table that includes
both field, but how do I get a custom msgbox instead of the index violation
error?

If it's just one record you are trying to add with a query I would go for the
general index idea and let Access handle it. Your appendquery will indeed not
add the record if the key is violated so in that case you could use the
append (run it, it won't be added anyway). With a dlookup you do make an
extra roundtrip.

dlookup("field to look for", "table where to look", "must meet this criteria")

hth
--
Maurice Ausum

I'm sure it's been covered here, but I couldn't find an exact explanation
that covers my problem:

I think I need DLookup to find out if a record exists in my table. I would
like to find out if [bidder_ID] exists for the [sale_date_ID] in the table
[tbl_cont_bidder]. If there is already a bidder_id for the given
sale_date_ID, then I would like to throw a message. If there is not, I would
run a query. I just don't get the DLookup part.