Unbound form for data entry on multiple front ends

[Nicholas Scarpinato]

I'm using an unbound form to input data into a record via VB code that runs
once the last field on the form us updated. This code opens the table, writes
the data to the appropriate fields, closes the table, then clears the form
and moves the focus back to the top control of the form.

My question is, when I split this database into front and back end sides and
put the back end out on the server, will I run into issues with the VB code
if by some happenstance two users happen to trigger said code at the same
time? I designed this DB this way purposely to try to avoid those issues, and
I'm going to make sure that the users who are going to access this database
have full permissions on the network location where the back end will be
stored. Also, I have other users who will be accessing the main data table
via queries. Will these queries prevent the first set of users from inputting
data off the entry form? I'm thinking that once the database is split, most
of these issues should resolve themselves... but I'm still concerned about
it. We want to implement this db for testing as early as Friday morning, and
I haven't been able to test it as a split db yet.

Any red flags in my process that I should be aware of, or am I in the clear?

 

[Pat Hartman]

You've created a lot of work for yourself to no purpose. Splitting the back
end doesn't impact how Jet works so if your only reason for using unbound
forms was to prevent update conflicts, you have not succeeded. Splitting
the database is done for other purposes such as to make updating the FE
easier and to reduce network load and improve stability of the Access app to
reduce the chance of corruption.

Whenever you are disconnected from your data, as you are with your method,
you actually have more of a potential for error since Jet isn't managing the
updates and serializing them for you. Inserts aren't too much trouble
except for the potential conflict with duplicate primary keys but updates
are downright dangerous unless properly managed. For example, userA reads
rec1 and userB reads rec1. UserA saves rec1 after making changes and then
userB saves rec1 after making different changes. UserA looses all his
changes because userB saved old data back to the table. You can get around
this by keeping a last updated date/time field in the record and using that
in the selection criteria in addition to the other fields so that your
update will fail if someone has updated the record between the time you read
it and want to save it.

If you use bound forms, Jet manages all of this for you. If two users open
a form to the same record, Jet knows. Then if userA makes any change to the
record, the record selector on his form turns to a pencil and the record
selector on userB's form turns to a circle with a line through it which
indicates that the record has been changed by someone else.

 

[Nicholas Scarpinato]

Comments in-line:

You've created a lot of work for yourself to no purpose. Splitting the back
end doesn't impact how Jet works so if your only reason for using unbound
forms was to prevent update conflicts, you have not succeeded. Splitting
the database is done for other purposes such as to make updating the FE
easier and to reduce network load and improve stability of the Access app to
reduce the chance of corruption.

The application is going to be hosted on the network, so splitting the
database is the only option for what we're trying to do. The unbound forms
were done as such for two reasons - one, to try to keep people from getting
record lock errors, and two, the main entry form is parsing data as it is
entered and making changes to the form accordingly as well as changing the
incomming data itself, so I can't very well bind it to a table. I tried
binding it to a table originally but I couldn't make it work.

Whenever you are disconnected from your data, as you are with your method,
you actually have more of a potential for error since Jet isn't managing the
updates and serializing them for you. Inserts aren't too much trouble
except for the potential conflict with duplicate primary keys but updates
are downright dangerous unless properly managed. For example, userA reads
rec1 and userB reads rec1. UserA saves rec1 after making changes and then
userB saves rec1 after making different changes. UserA looses all his
changes because userB saved old data back to the table. You can get around
this by keeping a last updated date/time field in the record and using that
in the selection criteria in addition to the other fields so that your
update will fail if someone has updated the record between the time you read
it and want to save it.

That's what I've been running into... and quite frankly, it's what I was
afraid would happen with doing it this way. But since I can't get my main
entry forms to cooperate when I have them bound to the data table, I don't
know what else to do. The problem is that most of the data coming into the
app is scanned in from barcodes and then has to be parsed by the app. One
barcode in particular contains three fields worth of information, so I can't
bind it to the table. I guess I could try to get my original version of the
form that was bound to the table working. I haven't had a lot of time to
experiement with the multi-user aspect of this app, I'm still waiting on our
IT department to get me a second test box so I can test that out.

If you use bound forms, Jet manages all of this for you. If two users open
a form to the same record, Jet knows. Then if userA makes any change to the
record, the record selector on his form turns to a pencil and the record
selector on userB's form turns to a circle with a line through it which
indicates that the record has been changed by someone else.

See, here's the problem... I can't EVER have that happen. Once a record is
entered, NOBODY should be able to change it. This app is being used for
inventory purposes, so each record represents one physical item in inventory.
Each user is entering a single item at a time as they work, so I can't have
two people modifying the same record. I need the database to say "Ok, this
record is currently being updated, so you have to start a new one."

Would an append query running off the unbound form solve this problem,
rather than using code?

 

[Nicholas Scarpinato]

You can get around this by keeping a last updated date/time field in the record
and using that in the selection criteria in addition to the other fields so that your
update will fail if someone has updated the record between the time you read it
and want to save it.

What happens if those two times are an exact match? That was the issue I ran
into yesterday during testing, my testing partner and I each happened to
enter in an item at the exact same time, down to the second (11:44:29 AM was
the timestamp). I put in a loop code last night that will check the tables
three times before proceeding with the update, but I haven't had the
opportunity to test that yet.

 

[Pat Hartman]

Both of you can acquire a record with the same time stamp but when you save
the record, you need to change the timestamp. That will cause the next
person's update to fail if you re-select the data with DAO or ADO and verify
the TS or if you use an update query that includes the TS in its criteria.

 

[Pat Hartman]

I wasn't suggesting that you not split the database, I was merely pointing
out that whether the tables are local or linked, Jet handles updates the
same way. And if your linked tables are SQL Server or some other RDBMS, the
database server takes care of record locking.

I don't really understand your update process. It sounds like you are
taking data from an external file created by a scanner and using that to
populate your inventory records. Given that, I don't understand why you
would use a form at all. I would just use an update query or a batch
process to take the data from the raw input file and append records to the
SQL tables.

If records can't ever be changed, then all data entry should be done with a
form whose data entry property is set to Yes and its Allow Edits, Deletions,
and Additions properties set to No.

 

[Nicholas Scarpinato]

First off, I guess I should explain the physical process a little more. I'm
building this database to handle returns of products from customers. So each
product returned is one item in the database. This is why I can't have
anybody editing the records as they're being entered. They DO require editing
down the line, but that is done by a different person who has nothing to do
with the initial receiving of the product.

I wasn't suggesting that you not split the database, I was merely pointing
out that whether the tables are local or linked, Jet handles updates the
same way. And if your linked tables are SQL Server or some other RDBMS, the
database server takes care of record locking.

I don't really understand your update process. It sounds like you are
taking data from an external file created by a scanner and using that to
populate your inventory records. Given that, I don't understand why you
would use a form at all. I would just use an update query or a batch
process to take the data from the raw input file and append records to the
SQL tables.

It's a barcode scanner, not a flatbed scanner. The barcode scanner is used
to scan in certain information off the product boxes, and other information
is manually entered. All of this information is entered on a form at the time
the product is received, and each product is received one at a time, and on a
seperate RMA number. So we're doing one entry every time we open a box.
Theoretically I COULD use an external file, or a temporary table, but I want
this process to automatically update the count of how many products have been
received for for each vendor and display that count in real time, along with
which receiving area is being used by what particular vendor. When the
returns department gets very busy, those counts can go up pretty quick, so
our person in charge of submitting the products to the vendor for credit
needs to know exactly how many products are in each location at a given time,
since some of the vendors have limits as to how much we can send at one time.

If records can't ever be changed, then all data entry should be done with a
form whose data entry property is set to Yes and its Allow Edits, Deletions,
and Additions properties set to No.

As for editing, the front end users are not going to be editing anything.
The flow of the database mirrors the product flow through the Returns
department, as follows:

1. Product is received by clerks
2. Product is processed into the system
3. All products for a specific vendor are grouped and sent back to the
appropriate vendor
etc.

Well, the people who are handling steps one and two don't need to worry
about anything except what they have in front of them. But later on down the
line in steps three and beyond, the process is out of their hands entirely,
and those records do require updates and changes, especially when the status
of each batch of records (they're grouped by vendor, number of products, and
what time they were cleared out of the returns receiving location and moved
to the pending ship location) changes. Now, that being said, if I make the
changes you suggested, will that force my main entry form to use a new record
every time the current one is committed to the table?

 

[Pat Hartman]

To answer your last question first - NO, you need to add your own code to do
that. Add a save button to the form and have the save button move to an
empty record after the save is complete. That will keep the flow moving.

Now that I understand the process better, I would definitely use a bound
form. Have the barcode put data into an unbound control. I don't think
that unbound controls fire the AfterUpdate event so you will need some
trigger to cause you to do something with the scanned data. You could use
the form's before update event or another button to parse the barcode data
and put it into the appropriate bound fields.

 

[Nicholas Scarpinato]

I actually went back and reworked that entry form as a bound form yesterday
and got that piece working. However, I'm still having problems in the second
table, the bin locations table. I have a second table which keeps track of
what bins are being used, what vendor the products in that bin belong to, and
how many products are in each bin. The problem has been in this table all
along, I'll get one bin tagged as receiving two products when it actually
only received one product and another bin should have been used for the
second product. I know we could just assign a bin to each vendor, but there's
no need to assign a bin that can hold 200 products to a vendor who we rarely
see any returns from. I've done this so that each end user will know exactly
where to put the product they've just checked into the system, because we
have issues where we'll get the same exact product, in the same box, but from
three different vendors. If we cross those products up, we won't get credit
on those products if we try to return them to the wrong vendors (and some
vendors will actually keep them, so not only do we not get the credit, but we
lose the product as well). Anyway, I went through the code that updates this
second table and reworked it to use queries instead of vb updates to the
table, and that ended up making things even worse. Now instead of getting 2
products counted for one vendor, I'm getting one product for the second
vendor and the first one gets completely overwritten. Also, the database
tells both users to use the same bin, which is exactly what I wanted to try
to prevent in the first place.

Here's what I need to have happen after each product is committed to the
database:

1. The product is added to the database with a date/time/username stamp.
2. The database checks it's bin locations table to see if any products from
that vendor have been received in since the last batch of products were sent
back to the vendor.
3. If it finds a bin that already has product from that vendor in it, add
one to the product count and display a message telling the user to use that
bin.
4. If it does not find a bin that already has product from that vendor in
it, find the next available bin and assign it to that vendor, add one to the
product count (which should be zero in this case), and display a message
telling the user that a new bin location is being used for that vendor, and
what bin to use.
5. Update the associated record in the main table with the bin location
information.

My problem lies in step 4, when there are two people trying to add product
for a vendor and neither vendor already has a bin assigned to them and they
both commit their records at the same exact time.

 

[Nicholas Scarpinato]

I don't think that unbound controls fire the AfterUpdate event so you will need
some trigger to cause you to do something with the scanned data.

I'm using the control's On Exit event as the trigger for the parsing code,
and I have error handling in that parsing code that basically exits the
function if there's no data in that field when you exit it (like if a user
tabbed through it by mistake), so it doesn't give the end user "Invalid use
of Null" errors and confuse the heck out of them.