Unbound data entry forms?

NachoMan

Registered User.
Local time
Today, 21:45
Joined
Sep 28, 2003
Messages
56
I have come across a boat load of posts in different forums where people tout the advantages of using unbound forms for data entry. There seem to be a lot of advantages to using unbound data entry form, particulary for multi-user systems. Among the advantages is having complete control over the data being added to the database. The major drawback to using unbound forms is, of course, the development time in coding all of the things to fully mimick a bound form. I understand that some people utilize append queries to add records (usually done explicitly with a cmd button).

I have several bound data entry forms in my database that I would like convert to unbound forms to have more control over the data. While I have heard a load of people praise their advantages, I have not seen anyone post an example of how they accomplish this themselves. Searching this and other forums, I have found only vague hints about how to go about it. If anyone has had success with using unbound forms as data entry forms, I would greatly appreciate some tips or an example of how to go about it.

Thanks in advance.
 
Nacho,

I have made several "unbound" forms. They are a LOT more
work and I personally don't see the advantage to them. You
need routines to copy all fields to/from your controls.

You need routines to Add/Update, as well as delete.

You need all your navigation routines Previous, Next, etc.

Again I don't think the extra work is worth it. As far as data
control goes. With all of Access's events (both form and
controls) you can put a line or two of code in critical spots.

If you invest the labor that the "head start" that bound
forms give you into making the app functional that should
be enough.

Sorry about the lack of specific "how tos", but unless they
are specified, I stay away from them.

Wayne
 
Unbound form problems?

Thanks for the quick response, Wayne.

It sure does seem like a lot of work, but I would like to get around certain problems I have with the bound forms.

I have a data entry form which has two subforms. The flow of the data entry starts with fields on the main form then switches to the two subforms and returns again to two memo fields on the main form. When the focus switches to the subforms, Access goes ahead and saves the data in the fields from the main form. I really do not want the record to be saved at this point. I want the record to be explicitly saved by the user once all of the fields are filled. The reason I want to do it this way is that I have a field [TranscriptNumber] which is automatically assigned by the database (a one up alpha-numeric number, not an autonumber). The transcript number cannot be changed by the user and there can be no gaps in the number order for the [TranscriptNumber] field. If a user goes to create a new Transcript record and the database assigns the transcript number TR10001 and subsequently deletes the record, the number TR10001 will be available for the next user.

My problem is that with multiple users creating records at the same time, I want to avoid the system giving two users the same number. Unbound forms limit the record locking to the instant the record is saved while bound forms keep record locking for potentially the entire time the record is being created, written. I want to it set up so that the users fill in the data for the main form and subforms and then click a "save" button. The "save" button will first assign the transcript number and then save the record (with an append query I assume) to the database tables. This will limit the record-locking time to a minimum and should avoid two records from being assigned the same transcript number.

One quick question. Are you aware of any problems I may encounter with unbound data entry forms and subforms? Will the subforms still link okay? Thanks again for your insight.
 
Problems with your plan.

1. The main form needs to be bound for the parent child links to work.
2. Assuming that you have enforced RI, you cannot add detail records before adding the main form record. This is why in a bound form, Access saves the current main form record when focus moves to a subform. It assumes that you have correctly defined your table relationships. Enforcing RI keeps you from entering or creating orphan records in the child tables.
 
Maybe I'll stick with the bound forms

Thanks for your reply, Pat. Unfortunately for me everything you mentioned sounds completely logical and my design plan is apparently ill-conceived. I suppose there would be a way that I could write some code on the BeforeUpdate event that would ask the user if he/she wanted to save the record. If the user clicked ok, then nothing else happens and the record is saved, otherwise the record could be undone at that point. Also, if the user clicks ok, the Transcript number could be assigned at that point. Not exactly what I was thinking from the start, but functionally the same, I suppose. Have you done something like this before or can you recommend a different strategy for explicitly saving the record?

-Lou
 
In the BeforeUpdate event of the main form, you can ask if the record should be saved. If the answer is yes, assign the key value and let the save proceed. If the answer is no, cancel the update. So the code will look like:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Msgbox("Do you want to create this record?", VbYesNo) = vbYes Then
    Assign your key value
Else
    Cancel = True
    Me.Undo
End If
End Sub

The BeforeUpdate event of the form will run (as long as the current record is dirty) whenever the user tabs into one of the subforms or attempts to leave the current record either by scrolling or by closing the form or database. It is NOT run, if the user changes focus to another form or report. That is why when you open a "pop-up" or a report, you need to force the current record to be saved in order for it to be available to the "pop-up" or report.
 
Thanks

All right. Thanks again for you help and insight, Pat.
 

Users who are viewing this thread

Back
Top Bottom