Check for new record in Network app

[Set me As newtype]

I have a customer database. Say there are 100 customers entered.

I have 2 (or more) networked users. The app sits with the next Customer # on their screens (101).

How do you usually check if there is any data entered by a user before allowing the other user to save customer 101?

I am thinking of using pessimistic locking if the Last Name field on any computer gets data, then perhaps a timer that would periodically check the record and automatically increment the other user's Customer # to 102???

What would you guys do?

(I'm using VB5 and DAO.)

[Siddharth Rout]

Just a thought...

Do you have a primary field (An Auto number) So that the screen picks up the latest number as the cust no? If more than 2 users are trying, then while saving, it again picks the last autonumber and updates the screen?

Like I said... just a thought...

[dee-u]

As koolsid has suggested a timer is one way which could periodically check for a certain parameter you wish to be checked.

Another option is a winsock that could broadcast to all other users that a certain user has made a new record.

And another thing, when just adding a record you could just get the new customer no. the moment you are saving the record so the user will always get the last customer no. if it is being updated. Just make sure it is marked as unique or set as PK.

[Set me As newtype]

Thanks for the replies.

I guess I was hoping that the many other questions I have would have been solved with seeing how this is "usually" done...

E.g., if two users have started entering a customer at about the same time, but one of them saves the record first, should the second user's customer number field (and # shown on his screen) be silently updated? Or should he get a message that the current # shown is no longer available? (I try to avoid using too many message boxes...)

[LaVolpe]

As Dee-U recommends, don't show the customer number until after the record has been appended to the database. The textbox can simply display [Customer Number - Pending] and after data has been appended, changed to reflect the actual number. Of course the textbox would either be disabled or locked so the user cannot enter/change the value. In your code, you would query the next customer number if it isn't an autonumber field. Otherwise, you can retrieve the autonumber value of the customer number after the record is appended. Why dao and not ado?

[Siddharth Rout]

Or should he get a message that the current # shown is no longer available?

This would be definitely irritating for the user...

One more option... Lock that space in the DB so no other user can pick that... If the the first user chooses to cancel the transaction, that cust no will be available for the next one... just a thought...

[Set me As newtype]

Guys,

I KNOW i need to lock the record (stated in my first post, along with the idea of a timer an pessimistic locking).

don't show the customer number until after the record has been appended to the database

My app is used for rapidly checking in and registering customers at an event. So I have a clipboard at each computer with the Cust #'s pre-printed down the left side of some registration forms. A person's name is entered, locking them to that Cust # in the database. Then they write their contact info on the clipboard, to be entered in the database later.

In this scenario, many times they are writing their address next to a # as someone is typing their name. Problems arise, as stated in my opening post, when two computers are trying to enter the same #...

So, since speed is the most important thing, it is very handy to show the Cust # on the screen during this process...

LaVolpe, I use DAO because it works (great!) for me. I've got tons of code written with it. I'll be switching to another language (soon?) and will likely need to learn something else at that time...

[LaVolpe]

Well, this might work but at least 1 exception should be handled.

1. As soon as the operator's screen displays empty entries for a new customer, immediately write a blank record using the next customer number retrieved from the databse. The logic is that if the program always retrieves the next available number, the blank record will 'reserve' the current one so no other operator can re-use it.

2. The exception is this. What if an operator then cancels or aborts? One idea would be when creating that blank record to fill in a field (like maybe the name) with a value that indicates the record was not saved/updated. This way, you can identify these later if needed or possibly re-use them (which doesn't sound like something you'd do here anyway).

Just some thoughts.

[Siddharth Rout]

Keith , that's what I meant in Post 6

If the user cancels the transaction, make it available for the next user...

[LaVolpe]

Keith , that's what I meant in Post 6

If the user cancels the transaction, make it available for the next user...

Sidz :: cool, we were on the same track.
Set me as newtype :: what sidz said in post #6

[dilettante]

What confuses me is how two operators can try to use the same customer # if the number comes off a clipboard sheet. Surely the sheets don't have the same list of #s on them, do they???

How could that ever work?

I'd think you'd want a 2nd update attempt to fail based on the database Customer table having a "unique" constraint on the customer # field. Then report "that customer is already registered as Jim Jones" or whatever name you find by querying when the update fails.


If you're working with a preset list of customer #s it's a lot like selling cars off a lot. No two are usually identical (options, the VINs, etc.) so you can't sell "the same car" twice.

[LaVolpe]

What confuses me is how two operators can try to use the same customer # if the number comes off a clipboard sheet. Surely the sheets don't have the same list of #s on them, do they???

That part confused me also, but I think the scenario may be like this
1. The sheets do have the same numbers
2. Each operator fills in the sheet only for the number shown in the program
3. When all sheets are collected, the filled in names can have many spaces (unused numbers--used by other operators) between entries. But when tallied, all numbers will have been used only once.

Of course I could be imagining the scenario incorrectly.

[Siddharth Rout]

Keith, I feel you are bang on target... This happens in my company (which is a BPO), We have Sheets with unique numbers and we need to update them in a 'Temp' software whenever the SAP goes down.... and are updated in SAP when it comes back....

[Set me As newtype]

1. The sheets do have the same numbers
2. Each operator fills in the sheet only for the number shown in the program
3. When all sheets are collected, the filled in names can have many spaces (unused numbers--used by other operators) between entries. But when tallied, all numbers will have been used only once.

This is exactly correct.

And it is why I've been imagining (as in my first post), locking the record as soon as a last name textbox gets a change. Then, the other system timers would see the change and auto-update the Cust # on those systems...

I think you guys are kinda saying the same thing.

I just wanted someone else to help me think through the logic and, perhaps, tell me the "standard" way this is handled. I hate overthinking stuff!

So is that about the best way to handle it?

Thanks very much for all the help on this, everyone!

[LaVolpe]

I just don't see a need for timers if sidz & my recommendations are considered.
1. Each time your app displays a screen for a new entry, it queries the database for the next customer number and writes a blank record. It then clears the user-entry textboxes or whatever & displays the customer ID of the blank record it just wrote to
2. When the data is entered into the program, and "Save/Submit" is clicked, it updates the record.

Every time step #1 is done, the app can only return the next number to be used and writes blank record. This way, no two operators can be referencing the same number. The customer number should be a Primary Key

If by chance, two operators reset their screens at the same time, then you might get an error if the primary key is duplicated, but that can be trapped and your app would then try again to get the next unused number & write a blank record for that new number until the error does not occur.

[Siddharth Rout]

locking the record as soon as a last name textbox gets a change.

You could do that or else like we have, A button which say "create new account"

[Set me As newtype]

I just don't see a need for timers

Because I want to have the newest available # shown at all times.

This enables the customer to start writing their contact info at the same time their name tag is being printed.

[LaVolpe]

I guess our ideas are not sinking in, because the potential solution I and koolsid posted can guarantee that. Either our ideas are not sinking in or both of us are way off base regarding understanding the situation.

If the following scenario creates a problem, then the posted non-timer solution by koolsid and myself may not work as is
1. You have 3 pcs up and running, each wrote blank records to the db & each are waiting for customers
:: Pc1 shows nr 101, Pc2 shows nr 102, Pc3 shows nr 103
2. A customer comes to pc #2 and will get customer number 102. Record is added to db & now pc2 shows nr 104
3. A customer comes to pc #3 and will get customer number 103. Record is added to db & now pc3 shows nr 105
4. Customer finally comes to pc #1 after 10 more customers have visited pc #2 & #3. This customer will get nr 101 and the pc will now show #115 after updating the db.

Does it really matter which customer ID is used at any one time, as long as no number is duplicated?

[Siddharth Rout]

each wrote blank records to the db & each are waiting for customers

Probably this can be avoided if the user at the pc writes to the record only when the customer is there...