Return to Main, Same Customer, New Record

[CanonMaiden]

I have a series of forms to allow users to add data to two tables.

User adds customer data in the first form then clicks a button to save
Customer info and navigate to the second form to enter item details. The
CustomerID is carried to the second form and opens to a new record.
Beautiful! (OpenArgs)

Form two has a subform which is a simple datasheet view filtered to show
only items relevant to that CustomerID. In theory the user will enter the
item details, click a button which saves the new record to the items table
and refreshes the main portion of the form to enable further item entries on
the same customer.

When I save the record it shows in the subform and is present in the table
however, the main form does not refresh at all. All attempts to refresh the
main form results in the itemID (autonum) failing to increment at best, or
losing the CustomerID at worst.

 

[Jeanette Cunningham]

CanonMaiden,
if you wish to enter further item entries on the same customer, there
wouldn't be anything to save on the main form.
The main form stays on the same customer, it's just the subform that moves
to the next record.
If you have the standard navigation buttons on the subform, user can just
click the new record button to add another item for the same customer.
You most likely don't need to use a save button.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[CanonMaiden]

Thank you Jeanette,
I hope I'm not making it more difficult for myself than need be.

The Customer table is the 'one' table. (Data entry via form1)
The Items table is the 'many'. (Data entry via form2)

The form in question- (form2)- main part of the form writes to the Items
table while it's subform displays all records in that same table for that
particular customer.

When I initally open form2 it is pre-populated with the customer number
carried from form1 and an item number autoassigned from the item table. I
populate the fields on the main portion of form2 and save. The new item is
then displayed in the subform (so far so good)...
Now I want to enter another item for that same customer. When I attempt to
enter item details for an additional item, the fields on the main portion of
form2 are still populated with data from the first item and more importantly
the item number is unchanged. So, when I attempt to save the new item it
simply overwrites the previous record.

 

[Steve]

Not sure if this will help, but I have a similar setup (however only like
your form2 not going from 1 to 2). After I take the info from the main form
and save it to the sub, all I do is clear the fields after you transfer the
info to the subform...

me.Field = Null
me.Field2 = Null


so on and so forth. Seems like that's all you really want to do ya know.
Just put those after you save the record to the subform.

 

[Steve]

forgot to add....in the same process just add one to the ID to have it move
with your items

 

[Jeanette Cunningham]

CanonMaiden,
thanks for the extra info - I can see the problem.
You have an extra form that is getting in the way and preventing an easy way
to enter the data.

Set it up so that there are only 2 forms - one is the main form and the
second form is the subform.
The main form is based on the customer table.
The subform is based on the Items table.
The link master field and link child field will be the ID for the customer.

On the main form you enter details about that customer.
In the subform you enter details about the items for that customer.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[CanonMaiden]

Hi Steve, Thank you. I tried vigilantly to do the same thing you suggested in
the macro builder. My eyes were crossed and I was still at ground zero.
That's when I posted my question. Can you break it down to a kindergarten
level for me? Please?

 

[CanonMaiden]

Thanks, Jeanette. Customer process dictates this scenario so I'm stuck with
it. For now anyway.

 

[Jeanette Cunningham]

Hi,
would you tell us what is so different about your customer process that you
can't take advantage of this excellent access feature of a main form for the
main table and a subform for the related table. This is one of the great
features of access to make your job of developing your database a lot
easier.
There are examples of this main form subform for customers and orders in the
Northwind sample database.
On the access help menu select Samples | Northwind


Jeanette Cunningham -- Melbourne Victoria Australia

 

[CanonMaiden]

My customer wishes to have their customer information on a single page. They
wish their Item details to be entered and displayed on an independent page in
the fashoin I described below.
The main drivers for this design is that Customer information will rarely
change compared to how often the Items (on auction), are added, then updated,
and closed.
The Item screen is navigated to via more than one screen, which, if I
remember correctly was the main cause for having the Item screen independant
of the Customers screen.
Displaying customer info on the same screen would cause the screen to need a
vertical scroll bar which is undesireable.

I happen to think it's a good design even if it doesn't fall into
Microsoft's cookie cutter templates.

Despite the sarcasm of the previous line, I am greatful to each and every
one of the wonderful people who take time to answer questions for those of us
who struggle. Requirements are my forte. This is my first attempt at coding
and I don't know that I would have been able to call it a success without the
help of this forum. HOWEVERRRRR I found this forum, my project is complete,
my customer is thrilled, I wasn't forced to settle for functionality that was
anything less than what my customer and I agreed would work best for their
business and I am a happy camper.
Thanks again to all of you!!!
With the utmost sincerity,
CanonMaiden

 

[Jeanette Cunningham]

Well done for getting Access to do exactly what you wanted!

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia