ledger form and binding vs queries

[JohnH]

I've designed a "ledger" form that generally acts like a datasheet
form, but uses text boxes, comboboxes, and a lot of code to act in a
much prettier way.

I first attempted to link the form to appropriate records (from 1
table) by setting the form's recordsource at runtime to a query string
and then using event driven procedures to create new records, update
records, and delete records (represented by line items in the ledger)
in the form's recordset (the controls in my ledger remaining unbound,
only the form is bound). This was leading to problems I wasn't sure
how to solve, such as retreiving field values from the form's
recordset, which doesn't seem to work the same way it does if you bind
a form at design-time, where you can reference fields by name even
when no control exists on the form for that field.

If the above is confusing, I'll put it in other words:
I was binding the form at runtime only so I would have a recordset I
could use to perform actions on based on the unbound controls in my
ledger and the code I've written that determines when a new line item
has been created, when a field in a line item has been edited, etc..
(I could have just created a recordset object (and maybe should have,
or should) but I was using the form's for visual debugging purposes.)


My questions are:
1) What would you recommend: That I use a) this approach, or b)run
Append, Update, and Delete queries for all necessary actions and
forget form binding.

If...
a)
then
How would I accomplish something like retreiving the autonumber
primary key for a new record when
I've ...DoCmd.GoToRecord , ,acNewRec...?

If
b)
then
How would I accomplish the above in this situation? If I run an
Append query (for a single record), how do I then find out what unique
record I've just created (the autonumber PK)?

I appreciate any advice I can get.

 

[Albert D. Kallal]

If the above is confusing, I'll put it in other words:

I was binding the form at runtime only so I would have a recordset I
could use to perform actions on based on the unbound controls in my
ledger and the code I've written that determines when a new line item
has been created, when a field in a line item has been edited, etc..
(I could have just created a recordset object (and maybe should have,
or should) but I was using the form's for visual debugging purposes.)

You are correct in your above observation. The solution is

a) to references data that does not have a corresponding control on the
form, use
me!NameOfFeild

if you bind the form, or actually place a text box control on the form with
the same name, then you can always use me.NameOfTextBox (which has the same
name as the field).

b) Simply place a text box on the form with the name you need, and then you
can use me.NameOffield

So, the preferred solution here is to simply use me!NameOfField, and then
ms-access will not care if there is a control by that name (or, if the form
is bound).

For forms that you bind at runtime, you can't use me.FieldName. However, if
you place a text box on the form, then even when the form is NOT bound, you
can still always go me.NameofTextBox.

The confusng issue here is that most deveopers (including me) use the
me.FieldName. however, for feilds that don't have a contorl on the form...I
well begun to adopt the me!Fieldname syntax....

One could actualy aruge that me.NameOfField is the wrong sytax to use. It is
just lucky that ms-access makes a collecion of fields for you when you bind
a form.

So, I tend to use:

me.nameofTextBox -- for refecing a text box on a form
me!NameOfField -- for referecing a field

Some access devlepoers actualy NEVER name the text box contorl the same as
the fieldname, and thus this can reduce a lot of confusing.

For most ms-access users..they don't give the above process much thought at
all..but, as you can see as you become more advanced..some thought(s) have
to be given to this issue.

 

[JohnH]

You are correct in your above observation. The solution is

a) to references data that does not have a corresponding control on the
form, use
me!NameOfFeild

if you bind the form, or actually place a text box control on the form with
the same name, then you can always use me.NameOfTextBox (which has the same
name as the field).

b) Simply place a text box on the form with the name you need, and then you
can use me.NameOffield

So, the preferred solution here is to simply use me!NameOfField, and then
ms-access will not care if there is a control by that name (or, if the form
is bound).

For forms that you bind at runtime, you can't use me.FieldName. However, if
you place a text box on the form, then even when the form is NOT bound, you
can still always go me.NameofTextBox.

The confusng issue here is that most deveopers (including me) use the
me.FieldName. however, for feilds that don't have a contorl on the form...I
well begun to adopt the me!Fieldname syntax....

One could actualy aruge that me.NameOfField is the wrong sytax to use. It is
just lucky that ms-access makes a collecion of fields for you when you bind
a form.

So, I tend to use:

me.nameofTextBox -- for refecing a text box on a form
me!NameOfField -- for referecing a field

Some access devlepoers actualy NEVER name the text box contorl the same as
the fieldname, and thus this can reduce a lot of confusing.

For most ms-access users..they don't give the above process much thought at
all..but, as you can see as you become more advanced..some thought(s) have
to be given to this issue.

Thank you for your thoughtful reply to my question Albert. That
clarifies things for me and I'm going to try to get this to work now.
I'll reply back if I have trouble. Thanks!

 

[JohnH]

Thank you for your thoughtful reply to my question Albert. That
clarifies things for me and I'm going to try to get this to work now.
I'll reply back if I have trouble. Thanks!- Hide quoted text -

- Show quoted text -

I got it! Thanks again Albert.