Unbound Subform

[tonyrusin]

Hi,

I'm using Access 2003. I've built an input form for users to submit
records. The form and all controls are unbound. When a user is
finished filling out the information, they click Submit and an append
query is ran to create a new record from the information on the input
form.

I would like to add a list of items to be included with their
submittal. Typically a temp table could be used to host the
information until the record was submitted. The problem is that
multiple users may be creating records from this input form
simultaneously. This means the temp table would have mixed information
from multiple users.

Is there a way to create some sort of unbound subform to host the temp
data until a user submits it?

Thanks in advance for any help.

- Tony

 

[Allen Browne]

Tony, that approach doesn't make sense in Access. Why not use a bound form,
and let Access handle all the multi-user issues for you? By choosing an
unbound form, you have lost almost all the benefits of using Access
(automatic data typing, checking sizes, the events of the form, and so on),
and created much unnecessary work for yourself, in addition to creating the
multi-user issues you highlighted.

Once you work out how useful bound forms are, you can then consider
splitting the database so each user has an independent copy of the front
end, so each user's local data is independent of the others. If that's a new
concept, see:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

 

[tonyrusin]

Hi Allen,

Thanks for your reply.

My database is split into FE / BE configuration. In fact, I'm in the
middle of migrating the BE to SQL Server because our company is
setting up a WAN. I will probably be using a replication setup to
maximize performance at each facility but I'm learning all this as I
go.

I may be just incorrect with my terminologies being that I'm a lone
developer. Everything I've learned in Access has for the most part
been on my own through books, web sites (including yours) and groups.

The issue I'm trying to avoid with using a bound form is that if it
was bound, a record is created when user starts to fill out the form,
not after they are completely finished. If they didn't finish, I
would have partially completed records. I'm trying to avoid having
the required field be driven by the table design and keep that on the
application side. Maybe the approach I'm using isn't optimal.
Thoughts?

- Tony

 

[Allen Browne]

Tony, could you use the BeforeUpdate event of the form to validate the entry
before Access attempts to write it to the table?

This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.SomeField) Then
Cancel = True
strMsg = strMsg & "SomeField is required." & vbCrLf
End If

If IsNull(Me.AnotherField) Then
Cancel = True
strMsg = strMsg & "AnotherField is Required." & vbCrLf
End If
If Me.SomeDate < DateAdd("yyyy", -1, Date) Then
Cancel = True
strMsg = strMsg "SomeDate is too old." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid"
End If
End Sub

That's basically what I do: use the form event to verify before the
attempted write.

 

[tonyrusin]

Allen,

A few questions:

1. When using this approach, how do I prevent the record from rolling
back to Autonumber once a record has been created?

2. I understand with the form's Data Entry property set to Yes, it
won't roll back to a record that was created since the last time the
form was closed. If a user creates a record and does not close the
form, it can roll back to that record (including the above
Autonumber). Is there a way to prevent this?

3. Some users (including myself) like to have some submittal button to
confirm that their record has been submitted into the system. Is
there some way to continue using this approach but make the form
function using a submittal button?

4. If someone presses ESC, the number picked by Autonumber is dropped
and the next number in sequence is used. This would mean I would end
up with a few holes in the sequence of numbers. Not that this is
necessarily a problem because it's just a placeholder but sometimes
it's nice to see a number that reflects the reality. Is there a way
to prevent it from sequencing to the next number?

Thanks,

- Tony

 

[Allen Browne]

Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

A few questions:

1. When using this approach, how do I prevent the record from rolling
back to Autonumber once a record has been created?

Not sure I understood this q. Is it the same as q.4?

2. I understand with the form's Data Entry property set to Yes, it
won't roll back to a record that was created since the last time the
form was closed. If a user creates a record and does not close the
form, it can roll back to that record (including the above
Autonumber). Is there a way to prevent this?

If you want to disable the possibility of the user going back to the record
they just created, you could probably Requery the form in its AfterInsert
event procedure. (Haven't tested it.)

3. Some users (including myself) like to have some submittal button to
confirm that their record has been submitted into the system. Is
there some way to continue using this approach but make the form
function using a submittal button?

The submit button just saves the record, e.g.:
RunCommand acCmdSaveRecord
That triggeres Form_BeforeUpdate where you perform your validation. Use
error handling, in case the save is cancelled.

If you want to insist that the record is *only* saved via the Submit button,
add a module-level boolean variable (General Declarations, top of form's
module.) Set it to True in the Click event of your button. If it's not True
in Form_BeforeUpdate, cancel, and jump out without the save. Reset it to
False in the error recovery of Submit_Click.

4. If someone presses ESC, the number picked by Autonumber is dropped
and the next number in sequence is used. This would mean I would end
up with a few holes in the sequence of numbers. Not that this is
necessarily a problem because it's just a placeholder but sometimes
it's nice to see a number that reflects the reality. Is there a way
to prevent it from sequencing to the next number?

Yes, Access assigns the AutoNumber as soon as you start creating a new
record, and never re-uses it if you abort.

As you say, the AutoNumber is just a unique identifier (not a sequence), so
treat it as such. In a single-user environment, you can use a Number field
instead of AutoNumber. In Form_BeforeUpdate, if it is Null, get the DMax()
and add 1. In a multi-user environment, that could yield duplicates (though
using Form_BeforeUpdate rather than Form_BeforeInsert helps.)

If it's a crucial requirement, it is possible to set create another table
for the purpose of storing the highest number used so far. Then in
Form_BeforeUpdate, lock this table, get the next number, assign to your
record, save your record, and then unlock the counter table. Add code to
handle conflicts (random time-outs and retries, limited count, possibility
of failing gracefully.) You must use a separate counter table for each table
that has this requirement: otherwise the locking becomes too restrictive.

HTH

 

[tonyrusin]

Allen,

Back on the issue of using bound / unbound form(s): part of my reason
for initially choosing not to bind them was because the migration to
SQL Server over a WAN.

By using SQL Server over a WAN and to help maintain performance, it is
recommended that I leave them unbound and use passthrough queries,
correct?

Also, would you happen to know of any decent literature for a Access
2007 FE / SQL Server 2005 BE setup that includes some how-to's on SQL
Server replication?

Thanks,

- Tony

 

[Allen Browne]

Also, would you happen to know of any decent literature for a Access
2007 FE / SQL Server 2005 BE setup that includes some how-to's on SQL
Server replication?

I don't have experience of doing that, Tony.
Perhaps someone who does have experience will respond.

 

[tonyrusin]

Any takers?

I don't have experience of doing that, Tony.
Perhaps someone who does have experience will respond.

 

[Douglas J. Steele]

The fact that you're using Access for the front end has no bearing on the
fact that you want to use replication on the back ened. You'd be far better
asking in a newsgroup related to SQL Server, such as
microsoft.public.sqlserver.replication.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Any takers?

 

[tonyrusin]

Cool, I check there then.

Thanks for your help guys. Your (MVP's) contribution to this society
makes so many more things possible. Keep up the good work!

- Tony