POS Terminals not updating Split Database in Real Time

[Nod_Swerdna]

I have added a POS module to my Store Management System. I am having an issue with the database. I have two invoicing POS terminals. When I create a new invoice on one POS terminal, the resulting invoice does not show up on the other POS terminal until I close and reopen the POS form on that terminal. (and Vice-Versa). I have previously split the database into Frontend and Backend.

Although the New Invoices are being created with the correct sequential invoice number from either terminal, when you look at the history from either terminal you only see recent invoices from that terminal. This means you have to return to the terminal that the invoice was created on to view a particular invoice.

I have patched the problem by closing and reopening the Invoicing form when you double click the 'New Invoice' button which forces the form to re-read the database. This results in a slight time lag and a flash as the form closes and reopens.

I am having similar issues with my other modules as well but they are not as pressing. If I solve the POS problem I can solve them all.

- Does anyone know of a coding trick to force a form to reread the database.
- If I switch to a SQL database will the problem go away.

Thanks in advance...
~Don

[Nod_Swerdna]

PS. This data lag only happens when I am creating a NEW invoice. If I update an Existing invoice the change shows up on the other terminal almost immediately...

[pbaldy]

Have you tried requerying the form?

Me.Requery

[Nod_Swerdna]

Thank you, yes I have. It's turning out to be a tricky problem for me. I've tried everything I can think of. I'm trying to think outside the box but my coding knowledge is limited to what I know. I was hoping a fresh mind might spark a new insite into the problem. I have 'patched' the problem by writing code that closes and reopens the form as I said above but I would rather make it an automatic update is possible, and the method I am currently using is a very crude solution to say the least.
Please let me know if you can help further.
Thanks...

[Missinglinq]

Me.Requery does the same thing that closing/opening the Form does, but it has to be applied to the POS Form on the terminal where the new Record wasn't added...not the one where it was added!

You could set the Timer Interval to an appropriate interval (a setting of 1000 = 1 second) and use code like this (untested) where UF_Rec is the name of a Field that is unique to a given Record:

If UF_Rec is Text:

Private Sub Form_Timer()
 
 Dim UF_Rec As String
   
   UF_Rec = Me!UniqueID
   Me.Requery
   Me.Recordset.FindFirst "[UniqueID] = '" & UF_Rec & "'"
 
End Sub

If UF_Rec is Numeric:

Private Sub Form_Timer()
 
 Dim UF_Rec As Integer
   
   UF_Rec = Me!UniqueID
   Me.Requery
   Me.Recordset.FindFirst "[UniqueID] = " & UF_Rec
 
End Sub

You probably don't want the Requery to fire every second, so try settings until you find something you can work with.

Alternatively, you could set the code behind a Command Button and let the users Requery when they need to.

Linq ;0)>

[Nod_Swerdna]

I'll try what you suggest. Thank you.