Unbound forms and dao recordset question

[NKK]

I have an unbound form I would like to use for entering new records (only).
I have deliberately chosen to separate entering new records and
viewing/updating previously entered records due to some factors of my
database that would make is difficult to accomplish both tasks with the same
form. On my data entry form I have three controls (among several others)
that all pertain to a single table (lu_submitters). These three controls are
a combobox (cboSubmitterID), a check box (cbxFacilityConfAgree) and a text
box (tbxFCADate). I have to combobox set up to look for submitter ID's in
the lu_submitters table; I would like cbxFacilityConfAgree to display the
data in lu_submitters.facility_conf_agree field and tbxFCADate to display the
data in lu_submitters.fca_date_received for the submitter_id that is chosen
in cboSubmitterID; if there is no data for those fields, I want the controls
to remain "open" so the user can entere data and then it can be "saved" when
the form is completed. I know I need to use a recordset object, but do I use
the openrecordset method or the find method. And what do I do with the
recordset if there is no associated data in those fields..do I close it
immediately or do I wait until the end of the data entry?

 

[Albert D. Kallal]

I have an unbound form I would like to use for entering new records (only).

You should probably expand a bit more here to make the case a little bit
more clear as to why you want use an unbound form here? It's really a lot of
extra work, and the problem is MS access does not work very well with
unbound forms at all VB.net or even the old VB6 has a whole bunch the
wizards designed around the whole concept and philosophy of using unbound
forms.

With access our forms have all kinds of special events and things built and
that allow us to control and even cancel the update of the underlying record
into the table. If you drop the use of unbound forms, then you getting the
worst of both worlds as we have no supports for un bound forms, and you wind
up not being able to use the forms objects model (features). For example, if
you edit some text boxes, you can't test the forms dirty property unless the
form is bound. the same thing goes for the before update event, if the user
does nothing then the before update event doesn't fire, but if they edit
something, then the event fires for you! With an unbound form you don't get
that luxury and the forms before update event don't fire (and you can't
cancel that event either).

Don't get me wrong, I think there are a lot of cases and situations in
access when it's proper to make an unbound form (and this case is usually
for prompt and dialog type boxes, not editing of data). For some simple
entry of data as you're proposing, it's really the wrong approach and all
you doing is wasting your time writing truckloads of code when NONE needs to
be written here at all...

On my data entry form I have three controls (among several others)
that all pertain to a single table (lu_submitters). These three controls
are
a combobox (cboSubmitterID), a check box (cbxFacilityConfAgree) and a text
box (tbxFCADate).

Why not just keep the form bound, and build a combo box that will "move" the
record to an existing ID when selected (thus not only will all of the other
pertinent fields be filled in automatically (without code), but at that
point the person who will be able to edit that data -- with your design,
you're going to have to develop a whole new different form that's can allow
editing of the data, and thus you're going to have to maintain two nearly
identical forms to manage this data when one should suffice.

I have to combobox set up to look for submitter ID's in
the lu_submitters table; I would like cbxFacilityConfAgree to display the
data in lu_submitters.facility_conf_agree field and tbxFCADate to display
the
data in lu_submitters.fca_date_received for the submitter_id that is
chosen
in cboSubmitterID; if there is no data for those fields, I want the
controls
to remain "open" so the user can entere data and then it can be "saved"
when
the form is completed.

when you see those two fields might be empty, are you planning to add a new
record in that case, or just one edit them?

You should simply bring up a form with the given submitter, and have this
form display all the pertinent information to this particular record. If
there is a need to enter multiple occurrences of the date received and other
information that belongs to that submitterID, then that should go into a
sub-form and you will never have to RE type that information or select it
again.

I know I need to use a recordset object, but do I
use
the openrecordset method or the find method.

Ok, if you must, you just best to open the recordset to the one record.

eg:

dim rst as dao.recordset
dim strSql as string

strSql = "select * from lu_submitters where id = " & me.cboSubmitterID
set rst = currentdb.OpenRecordSet(strSql)

Keep in mind if you limit your combo box to the legal list of IDs, then they
can never select an ID that's not in existence anyway, this saves you having
to bother to check if the id don't exist...

And what do I do with the
recordset if there is no associated data in those fields..do I close it
immediately or do I wait until the end of the data entry?

Why do you care if there is data in a couple of the twenty fields? Why
would you bother to close a record set at this point? I see no reason to do
this. Your problem is you're going to have to test in the form if the users
actually edited or changed anything, and you can't use the forms dirty
property because the forms not bound.

If you use a bound form, then you simply use the forms before update event
which only fires if the data actually been changed on the form. So, I would
still think really long and hard about you design of using an unbound form,
because it's a lot of work and it accomplishes almost nothing for you.

This whole process and what you're trying to achieve becomes almost a
trivial issue with very little if any code to do this if you choose a bound
form.