auto lookup (1 Viewer)

[davidg47]

I am a little bit of a novice when it comes to using VBA with Microsoft Access. I am building a form called "orders" with a db called "orders1". There is the ability to have 10 orders displayed per form with 5 fields per order that display important information to consider when filling out an order. Is there a way so that as the last field in an order line is filled out that a query or something could run against the "orders1" db to see if an order with the same information already exists? For example: If "Company#1" orders "Item#2" with "ProductRights#A", "TerritoryRightsA", to be used between the dates of "Jan-1-03" and "Dec-31-03", after the last field in the line is filled out, a check would be done of the "orders1" db to see if an order for the same Item# with the same ProductRights, same TerritoryRights, and at any time that falls within the same time period as the newly entered order, then a MsgBox would pop up informing the user that another company or even the same company has placed the same order sometime during the same time period as the order they just entered? The difficult part of this I guess is that even if the new order overlaps an existing one by one month in the date range, then a message needs to warn the user of the existence of the order in the db.
I have attached a .jpg of the orders form to maybe help you understand more clearly. The fileds I have highlighted are the ones on each line order that I need to check for.
I'm not sure this is the right place to post this, so I will also post it in the Forms Forum.
Thanks for all your help.

 

[WayneRyan]

David,

There are a couple ways of doing this:

On the AfterUpdate event of your last field you can
(1) check for the existence of the other four fields
(2) use the DLookUp function to see if this is a duplicate

Or

On the AfterUpdate event of your last field you can
(1) check for the existence of the other four fields
(2) Use a recordset to check for the duplicate
More complicated ... but more efficient and readable.

hth,
Wayne

 

[davidg47]

Thanks for your quick reply, and for the advice you have offered. But while I have an idea of what you're talking about, I have no idea what the methods are for doing it. Is there a place I can go to and read how to enter this code, and how to get it to work? As I said, I am a novice at VBA.
Thanks again, David

 

[WayneRyan]

David,

If you installed the help files, you can just lookup the
DLookUp function. Or you could probably search for
it in this forum.

If you would like an example of using recordsets:

Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrendDb
sql = "Select * from YourTable " & _
"Where Field1 = '" & Forms!YourForm!Field1 & "' and " & _
" Field2 = '" & Forms!YourForm!Field2 & "' and " & _
" Field3 = '" & Forms!YourForm!Field3 & "' and " & _
" Field4 = '" & Forms!YourForm!Field4 & "' and " & _
" Field5 = '" & Forms!YourForm!Field5 & "'"
Set rst = dbs.OpenRecordSet(sql)
If Not rst.EOF and Not rst.BOF Then
MsgBox("Duplicate record.")
End If

Wayne