Two fields two forms that update each other

[NigelD]

In Access 2007 I have two forms - one called Customers and the other called SWZ. There is no relationship between the two forms. The SWZ form has two fields Account Number and Application Number. The Account Number field is prepopulated with Numbers.

The customer form also has two fields one called Appno and the other is called AC number . Appno is prepopulated with numbers.

I need to do the following from the customer form:

Open the swz form and populate the first record Application Number field from the Appno field on the Customer form

Then take the Account Number from the same record in the SWZ form and populate the AC number field in the Customers form.

I havent got a clue where to start.

Help please

[PianoMan64]

In Access 2007 I have two forms - one called Customers and the other called SWZ. There is no relationship between the two forms. The SWZ form has two fields Account Number and Application Number. The Account Number field is prepopulated with Numbers.

The customer form also has two fields one called Appno and the other is called AC number . Appno is prepopulated with numbers.

I need to do the following from the customer form:

Open the swz form and populate the first record Application Number field from the Appno field on the Customer form

Then take the Account Number from the same record in the SWZ form and populate the AC number field in the Customers form.

I havent got a clue where to start.

Help please

Hello NigelD,

Welcome to Byte.

To answer your question, what I would suggest to you is simply have the field you want to populate with a combox and fill it will the values from the Customer table that the customer Form is based on. and the same on the customer form with the table that the SWZ form is also based on. This way you can access the information without having one or the other form open at the same time.

It is never good practice to try to create a link between two items with both being created at the same time. To much room for failure.

Hope that helps,

Joe P.

[NigelD]

To answer your question, what I would suggest to you is simply have the field you want to populate with a combox and fill it will the values from the Customer table that the customer Form is based on. and the same on the customer form with the table that the SWZ form is also based on. This way you can access the information without having one or the other form open at the same time.

It is never good practice to try to create a link between two items with both being created at the same time. To much room for failure.

I had thought of this, unfortunately the SWZ table contains unique ID's in the Account number field Yes I can look up the next free Account number using a combo box and saving the Account number in the Customer.AC Number field the problem then becomes one of filling the SWZ.Application number field from the customer.App no field without taking two actions

The SWZ query is based on Application Number is not null which ensures that duplicate Account Numbers dont appear in the Customer table.

So is there a way when I select the Customer Account number using the combo box a pop up form opens and selects the record from the combo box and populates the SWZ Application Number field or something along those lines

[PianoMan64]

Hey NigelD,

So what is the relationship between the two table?

1. Is it that you can have Multiple SWZ records with One customer?
2. Is it that you can have Multiple Customers under one SWZ record?
3. or is it simply a one to one ratio between the two tables, and you're wanting to create an linking ID between them to tie them together as one Item?

Let me know, cause what you've explained doesn't make much sense to me.

Thanks,

Joe P.