Auto populate info in a form using a different source table

[Chey]

I already have a form built based on Table #1. Now I am trying to develop a
system where whenever I have Type A customer contract I just press a button
on my form and it populates the fields with the std information.

to accomplish this I developed a Table #2 and tried to link/bring the info
to my form using:
1 - a macro......
2 - a update query.....
3 - a sub-form

None of the solutions I tried worked...I may be doing some wrong....Can you
help me out? Thanks

 

[Maarkr]

depends...If you don't have many fields, you could just do a lookup
Me.textboxname=DLookup("city","tblZipCode", "ZipCode=[ZIP]")
If you have lots of data, I would just link the values from your mainform to
the subform, ie, ContractType, to display your data in the subform. How did
u try to build your main/subform? Did u build your main/subform based on a
query sjowing the linked tables?

 

[Mrs. Ugh]

A brute force method:
In your Customer Contract Type field, add an After Update event that says If
Me![CustomerContractType] = A then...and then assign a value to each field on
the form (Me![fieldname1] = standardvalue1, Me![fieldname2] = standardvalue2,
etc.)
Jill

 

[Chey]

There are 41 fields. The subform It is a regular form built in datasheet
format. The only problem I see to do a Dlookup is that I will only use the
subform when the contract is standard that's why I wanted to insert a button
in my main form.

When you tell me to do a Dlookup where would that be placed?

Both the form and the subform are based directly on a table, in this case
table#1 and table#2 respectively.

As soon as I enter a new agreement in my form Table #1 is updated.

depends...If you don't have many fields, you could just do a lookup
Me.textboxname=DLookup("city","tblZipCode", "ZipCode=[ZIP]")
If you have lots of data, I would just link the values from your mainform to
the subform, ie, ContractType, to display your data in the subform. How did
u try to build your main/subform? Did u build your main/subform based on a
query sjowing the linked tables?

I already have a form built based on Table #1. Now I am trying to develop a
system where whenever I have Type A customer contract I just press a button
on my form and it populates the fields with the std information.

to accomplish this I developed a Table #2 and tried to link/bring the info
to my form using:
1 - a macro......
2 - a update query.....
3 - a sub-form

None of the solutions I tried worked...I may be doing some wrong....Can you
help me out? Thanks

 

[Chey]

This is Visual basic right? I don't know how to right in Visual Basic.

I tried something similar with a macro using SetValue and it did not work.

A brute force method:
In your Customer Contract Type field, add an After Update event that says If
Me![CustomerContractType] = A then...and then assign a value to each field on
the form (Me![fieldname1] = standardvalue1, Me![fieldname2] = standardvalue2,
etc.)
Jill

I already have a form built based on Table #1. Now I am trying to develop a
system where whenever I have Type A customer contract I just press a button
on my form and it populates the fields with the std information.

to accomplish this I developed a Table #2 and tried to link/bring the info
to my form using:
1 - a macro......
2 - a update query.....
3 - a sub-form

None of the solutions I tried worked...I may be doing some wrong....Can you
help me out? Thanks

 

[John W. Vinson]

I already have a form built based on Table #1. Now I am trying to develop a
system where whenever I have Type A customer contract I just press a button
on my form and it populates the fields with the std information.

to accomplish this I developed a Table #2 and tried to link/bring the info
to my form using:
1 - a macro......
2 - a update query.....
3 - a sub-form

None of the solutions I tried worked...I may be doing some wrong....Can you
help me out? Thanks

Do you just want to *display* the information or do you want to copy 41 fields
from one table into a different table? If the latter... why? Storing data
redundantly is never a good idea!

It all starts with the tables. What's your underlying table structure, and
what's "the std information" for a "Type A customer"?

 

[Chey]

Table #1 has all the customer agreements signed with our company, with all
the fields related to an agreement.

Table #2 I built so for standard agreements (few of them) I just need to
click a button on my form and transfer the information without having to
loose time typing them. Once the information is transfered to my main form
(which is linked to Table#1) everything is consolidated in one place.

Table #2 has selected fields from Table #1, a standard agreement won't fill
in all the fields as a customized one does.

 

[John W. Vinson]

Table #1 has all the customer agreements signed with our company, with all
the fields related to an agreement.

Table #2 I built so for standard agreements (few of them) I just need to
click a button on my form and transfer the information without having to
loose time typing them. Once the information is transfered to my main form
(which is linked to Table#1) everything is consolidated in one place.

Table #2 has selected fields from Table #1, a standard agreement won't fill
in all the fields as a customized one does.

I'd suggest running an Append query in that case, with criteria to select only
the one record that you want to copy. You can run the query using a command
button on the form, and Requery the form to show the newly added record.

 

[Chey]

I tried to do an append query but it does not work with Foms only works when
you append directly on a table instead of a form

 

[John W. Vinson]

I tried to do an append query but it does not work with Foms only works when
you append directly on a table instead of a form

Exactly.

Data is not stored in Forms. Data is not read from Forms. Data exists in
tables, *and only in tables*. A Form is just a window to display the data
that's stored in a table.

If you want to create a new record... *do so in the table*, and then display
it on a form.

 

[Chey]

Is there any way to do through a form? My users use the form to enter a new
contract info or to view and if they have to go behing the form and enter
directly into the table or use a append query it will complicate....how would
that work?

The info I have in my std agreement still need to adde customer name, start
and end date of the agreement etc... That's why I was trying to find a way to
transfer the info into the form and the user complets the remaing fields as
described above.