Writing\Saving to tables immediately after DAO INSERT

[BRZ-Ryan]

I am using VBA\DAO to write new records to a table in a shared database (front-end/back-end), which obviously has multiple users. This database is for scheduling meeting rooms and we are trying to avoid availability conflicts when more than 1 person tries to select a time slot(s) at the same time.

The way it works is, when the form loads, it loads all available slots in the listbox. If 2 users open it prior to the other scheduling the desired slot, they can both see and select the same slots. If one person picks the slots, I want the second person to get an error\msgbox if they pick the same slot(s) stating the slots were already selected. I have most of this taken care of already and works if I write one persons selections and then re-visit the table shortly after, but it seems as if the data isn't being physically written to the table immediately, maybe just sitting in memory.

The table being written to is not bound to a form, it's manipulated only through VBA code. How can I write\save changes to this table immediately after my DAO INSERT INTO query, which is what writes selected time slots to my table so it can be found by my DLookUp call (used to check for already selected slot(s)) within a second or less?

How slots are written to my table:

CurrentDb.Execute "INSERT INTO Scheduled (UserName,ApptDate,ApptTime,ApptID) VALUES ('" & strUser & "',#" & strDate & "#,#" & arrTimes(lstStart.Column(0, lstStart.ListIndex)) & "#, " & lngApptID & ");"

Thanks!

[orange]

Are you saying that there is some noticeable lag time after the execution of the Insert?
Have you tried inserting a Select query to read the Schedule immediately after running the Insert. Or pehaps a msgbox with your DCount?

[BRZ-Ryan]

The issue is it is not updating the data immediately because DLookup comes up with no results when a INSERT has occurred which should make the DLookUp have results.

I tested this two ways, running two front-ends on my own machine, and running one myself and my boss on another machine. I selected a time slot on one front end and the same slot on another front end. I then click schedule on both, a second or less apart, and I get scheduled twice on the same slots. The one being scheduled second in line should get a msgbox block stating the slot is taken. It looks like the data is not hitting the table instantly so dlookup is not finding it.

[rpeare]

are you issuing a me.refresh command between the insert an the next time you do a dcount?

though it sounds like you will have to issue a me.refresh BEFORE you perform the insert as well to make sure it's actually checking the most updated set of records.

[BRZ-Ryan]

No I am not.. I will try. What exactly does me.refresh do? My form is not bound to this table, if that matters.

[rpeare]

me.refresh forces an update of your data, so in cases like yours where you're using an unbound form across multiple users one of your users should be 'first' and the second addition should be refused or altered.

[orange]

I thought that the second or subsequent attempts to save would get a message that the record was being held/used by another user.
I was surprised when you said

and I get scheduled twice on the same slots.

I hope rpeare's suggestion works.