set information automaticlly in fields-forms

I did write a database for my business - i'd like to administrate the items,
coustems etc...

i made a table for items, coustomers, invoice, orders and product group
etc..

after i made a form, where i can fill out the order from the coustomers, and
i want after i did wrote the coustomer number ( ID ) that in this form the
other fields, name, surname, street zip code etc.... field out automaticly .
I think i have to write a query. that compare the ID in the order_table with
the ID in the coustomer_table with a marcro , first i must take the fields
and then with a macro put in the dates from the coustomer_table? But it
doesn't work.

Then the same with the products ( items ) after i wrote the name of the
item, all fields should be filled out automaticly -

In the table for the productitems are all products only one time, in the
order_table i can put in the items always, not only one time!

Na rijp beraad schreef peter junker :
I did write a database for my business - i'd like to administrate the items,
coustems etc...

i made a table for items, coustomers, invoice, orders and product group
etc..

after i made a form, where i can fill out the order from the coustomers, and
i want after i did wrote the coustomer number ( ID ) that in this form the
other fields, name, surname, street zip code etc.... field out automaticly .
I think i have to write a query. that compare the ID in the order_table with
the ID in the coustomer_table with a marcro , first i must take the fields
and then with a macro put in the dates from the coustomer_table? But it
doesn't work.

Then the same with the products ( items ) after i wrote the name of the
item, all fields should be filled out automaticly -

In the table for the productitems are all products only one time, in the
order_table i can put in the items always, not only one time!

After you filled in the ID and press enter (afterupdate for example),
you can use the DLookUp function to find the customer data in the
customer table.Like:
txtCustomerName=DLoookup("[CustomerName]", "Customers", "[CustomerID] =
" & txtCustomerID )
txtCustomerAddress=DLoookup("[CustomerAddress]", "Customers",
"[CustomerID] = " & txtCustomerID )
etc.
You can do that in code or using a macro (SetValue-action)

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

Hello Gijs,

sorry, but I didn't get it. I'm not so fit in programming access - the
database works, but i have to wrote all fields - if i wrote a invoice for a
coustomer i have to put all dates in the fields, access doesn't fill out
automaticlly. Id like to make it with a macro, but i receive always a note,
that the field isn't found ? IF I take setvalue the first field is the field
id like to update and under the first i have to put in where i have to take
the dates ? didn't work, maybe you can explain as a macro, thanks Peter

"Gijs Beukenoot" schrieb im Newsbeitrag
...
Na rijp beraad schreef peter junker :
I did write a database for my business - i'd like to administrate the
items,
coustems etc...

i made a table for items, coustomers, invoice, orders and product group
etc..

after i made a form, where i can fill out the order from the coustomers,
and
i want after i did wrote the coustomer number ( ID ) that in this form
the
other fields, name, surname, street zip code etc.... field out
automaticly .
I think i have to write a query. that compare the ID in the order_table
with
the ID in the coustomer_table with a marcro , first i must take the
fields
and then with a macro put in the dates from the coustomer_table? But it
doesn't work.

Then the same with the products ( items ) after i wrote the name of the
item, all fields should be filled out automaticly -

In the table for the productitems are all products only one time, in the
order_table i can put in the items always, not only one time!

After you filled in the ID and press enter (afterupdate for example),
you can use the DLookUp function to find the customer data in the
customer table.Like:
txtCustomerName=DLoookup("[CustomerName]", "Customers", "[CustomerID] =
" & txtCustomerID )
txtCustomerAddress=DLoookup("[CustomerAddress]", "Customers",
"[CustomerID] = " & txtCustomerID )
etc.
You can do that in code or using a macro (SetValue-action)

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

peter junker bracht volgend idée uit :
Hello Gijs,

sorry, but I didn't get it. I'm not so fit in programming access - the
database works, but i have to wrote all fields - if i wrote a invoice for a
coustomer i have to put all dates in the fields, access doesn't fill out
automaticlly. Id like to make it with a macro, but i receive always a note,
that the field isn't found ? IF I take setvalue the first field is the field
id like to update and under the first i have to put in where i have to take
the dates ? didn't work, maybe you can explain as a macro, thanks Peter

"Gijs Beukenoot" schrieb im Newsbeitrag
...
Na rijp beraad schreef peter junker :
I did write a database for my business - i'd like to administrate the
items, coustems etc...

i made a table for items, coustomers, invoice, orders and product group
etc..

after i made a form, where i can fill out the order from the coustomers,
and i want after i did wrote the coustomer number ( ID ) that in this form
the other fields, name, surname, street zip code etc.... field out
automaticly . I think i have to write a query. that compare the ID in the
order_table with the ID in the coustomer_table with a marcro , first i must
take the fields and then with a macro put in the dates from the
coustomer_table? But it doesn't work.

Then the same with the products ( items ) after i wrote the name of the
item, all fields should be filled out automaticly -

In the table for the productitems are all products only one time, in the
order_table i can put in the items always, not only one time!

After you filled in the ID and press enter (afterupdate for example),
you can use the DLookUp function to find the customer data in the
customer table.Like:
txtCustomerName=DLoookup("[CustomerName]", "Customers", "[CustomerID] =
" & txtCustomerID )
txtCustomerAddress=DLoookup("[CustomerAddress]", "Customers",
"[CustomerID] = " & txtCustomerID )
etc.
You can do that in code or using a macro (SetValue-action)

--

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

peter junker gebruikte zijn klavier om te schrijven :
Hello Gijs,

sorry, but I didn't get it. I'm not so fit in programming access - the
database works, but i have to wrote all fields - if i wrote a invoice for a
coustomer i have to put all dates in the fields, access doesn't fill out
automaticlly. Id like to make it with a macro, but i receive always a note,
that the field isn't found ? IF I take setvalue the first field is the field
id like to update and under the first i have to put in where i have to take
the dates ? didn't work, maybe you can explain as a macro, thanks Peter


Dates?

Oh well...

First, determine where you want to fill these dates (at what point in
your form I mean)
Then, goto that control
Press properties and go to the AfterUpdate Event
Click the three dots and choose macro
Name the macro
In the list, choose SetValue
'Downstairs', right-click and choose build (exporession builder it's
called I think)
Select the form and then the control in the dialog that appears. Double
click that last one
In the second field, enter the expression. For today, the expression
could be =Now()
Or for a lookup, it could be =DLookup(as mentioned earlier)

You might be missing the = sign or you might have the item wrong if you
get an error.

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

Hello Gijs,

i did read and search in a book for Access with VBA , Programming etc....but
it doesn't work - i receive always an error also with =

I try to explain better now.

kunden_tbn (for all coustomers)
articel_tbn (for all items, products)
bestellungs_tbn ( for all orders )

i made a form with all fields of the bestellungs_tbn - including
coustomer_id.

in this form i put in combo boxes for coustomer_id, base on a query for all
infos for coustomers ( street,zip code, city etc...) this list contains all
coustomers and i can choose one - the coustomer_id can i put in the field.

after i put this coustomer_id, id like that access fill out the field,
street, zip code, city etc... for this coustomer.

i choose the field ( combo box ) for the coustomer_id and pressed
properties, i choose after update and the ...points - then i choose to
create a macro - set a name and start with setvalue - there are 2 fields for
setvalue ( down )

1. field: ??? i put in the field of the [Kunden_tbn].[KundenID.] - there
are the id to compare.
2: expr.: here i put in
=Dlookup([Kunden_ID];[Kunden_tbn];[Bestellungs_tbn].[KundenID]=[Kunden_tbn].
[KundenID])

after that i receive always the error " Kunden_tbn isn't found or contained
in the form.

Maybe its better in the bestellungs_form to out a button - i put the number
of the coustomer, then i press the button for a query of macro, that i can
search the coustomer date in a pop up form - then with a click to a button
in the pop up form i can put in the main_form ( bestellungs_form ) . an idea

thanks Peter



"Gijs Beukenoot" schrieb im Newsbeitrag
...
peter junker gebruikte zijn klavier om te schrijven :
Hello Gijs,

sorry, but I didn't get it. I'm not so fit in programming access - the
database works, but i have to wrote all fields - if i wrote a invoice
for a
coustomer i have to put all dates in the fields, access doesn't fill out
automaticlly. Id like to make it with a macro, but i receive always a
note,
that the field isn't found ? IF I take setvalue the first field is the
field
id like to update and under the first i have to put in where i have to
take
the dates ? didn't work, maybe you can explain as a macro, thanks Peter


Dates?

Oh well...

First, determine where you want to fill these dates (at what point in
your form I mean)
Then, goto that control
Press properties and go to the AfterUpdate Event
Click the three dots and choose macro
Name the macro
In the list, choose SetValue
'Downstairs', right-click and choose build (exporession builder it's
called I think)
Select the form and then the control in the dialog that appears. Double
click that last one
In the second field, enter the expression. For today, the expression
could be =Now()
Or for a lookup, it could be =DLookup(as mentioned earlier)

You might be missing the = sign or you might have the item wrong if you
get an error.

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

peter junker gebruikte zijn klavier om te schrijven :
Hello Gijs,

i did read and search in a book for Access with VBA , Programming etc....but
it doesn't work - i receive always an error also with =

I try to explain better now.

kunden_tbn (for all coustomers)
articel_tbn (for all items, products)
bestellungs_tbn ( for all orders )

i made a form with all fields of the bestellungs_tbn - including
coustomer_id.

in this form i put in combo boxes for coustomer_id, base on a query for all
infos for coustomers ( street,zip code, city etc...) this list contains all
coustomers and i can choose one - the coustomer_id can i put in the field.

after i put this coustomer_id, id like that access fill out the field,
street, zip code, city etc... for this coustomer.

i choose the field ( combo box ) for the coustomer_id and pressed
properties, i choose after update and the ...points - then i choose to
create a macro - set a name and start with setvalue - there are 2 fields for
setvalue ( down )

1. field: ??? i put in the field of the [Kunden_tbn].[KundenID.] - there
are the id to compare.

No, this should be the field you want to fill in(the field that holds
the customer name on screen for example)

2: expr.: here i put in
=Dlookup([Kunden_ID];[Kunden_tbn];[Bestellungs_tbn].[KundenID]=[Kunden_tbn].
[KundenID])

The first part now searches for Kunden_ID. You already know that one.
If you change it to Kunden_Name and change the last part to "[KundenID]
= [name of your combobox]" then, it would retrieve the Kunden_Name
from the table Kunden_tbn where the KundenID equals the Kunden_ID from
the combobox.

So, for example, you're form holds:

Kunden_ID (combobox called cmbKunden)
Kunden_Name (textbox called txtName)
Kunden_Adresse (textbox called txtAdresse)
undsoweiter

then the afterupdate-event of your combobox could fill in name and the
address with the macroactions:
SetValue
Kunden_Name
=DLookup("Kunden_Name", "Kunden_tbn", "Kunden_ID=" & cmbKunden)
SetValue
Kunden_Adresse
=DLookup("Kunden_Adresse", "Kunden_tbn", "Kunden_ID=" & cmbKunden)

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

Hello Gijs,
thanks again, but it doesnt work - dont understand why. !

field: 1st field: i put in!
[Vorname] - ( i have to choose from the table Kunden_tbn or the form? )

exp: 2nd field: i put in !
= DLookUp("[Vorname]";"[kunden_tbn]";"[KundenNr]" = &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29])
( combox field for
coustomer )

it doesn't work - access gives me an error message.


Did I understand right:
1 field - what i want to update
2 field - the command for the expr. - where the dates are and then to put
in.

The combo box field - based on a query returns with all dates of the
coustomers - after i did update only with the coustomer Id - the other -
Vorname,Nachname etc. is filled out automaticlly.

thanks peter






"Gijs Beukenoot" schrieb im Newsbeitrag
...
peter junker gebruikte zijn klavier om te schrijven :
Hello Gijs,

i did read and search in a book for Access with VBA , Programming
etc....but
it doesn't work - i receive always an error also with =

I try to explain better now.

kunden_tbn (for all coustomers)
articel_tbn (for all items, products)
bestellungs_tbn ( for all orders )

i made a form with all fields of the bestellungs_tbn - including
coustomer_id.

in this form i put in combo boxes for coustomer_id, base on a query for
all
infos for coustomers ( street,zip code, city etc...) this list contains
all
coustomers and i can choose one - the coustomer_id can i put in the
field.

after i put this coustomer_id, id like that access fill out the field,
street, zip code, city etc... for this coustomer.

i choose the field ( combo box ) for the coustomer_id and pressed
properties, i choose after update and the ...points - then i choose to
create a macro - set a name and start with setvalue - there are 2 fields
for
setvalue ( down )

1. field: ??? i put in the field of the [Kunden_tbn].[KundenID.] -
there
are the id to compare.

No, this should be the field you want to fill in(the field that holds
the customer name on screen for example)

2: expr.: here i put in

=Dlookup([Kunden_ID];[Kunden_tbn];[Bestellungs_tbn].[KundenID]=[Kunden_tbn].
[KundenID])

The first part now searches for Kunden_ID. You already know that one.
If you change it to Kunden_Name and change the last part to "[KundenID]
= [name of your combobox]" then, it would retrieve the Kunden_Name
from the table Kunden_tbn where the KundenID equals the Kunden_ID from
the combobox.

So, for example, you're form holds:

Kunden_ID (combobox called cmbKunden)
Kunden_Name (textbox called txtName)
Kunden_Adresse (textbox called txtAdresse)
undsoweiter

then the afterupdate-event of your combobox could fill in name and the
address with the macroactions:
SetValue
Kunden_Name
=DLookup("Kunden_Name", "Kunden_tbn", "Kunden_ID=" & cmbKunden)
SetValue
Kunden_Adresse
=DLookup("Kunden_Adresse", "Kunden_tbn", "Kunden_ID=" & cmbKunden)

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

Na rijp beraad schreef peter junker :
Hello Gijs,
thanks again, but it doesnt work - dont understand why. !

field: 1st field: i put in!
[Vorname] - ( i have to choose from the table Kunden_tbn or the form? )

The form

exp: 2nd field: i put in !
= DLookUp("[Vorname]";"[kunden_tbn]";"[KundenNr]" = &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29])
( combox field for
coustomer )

Sounds almost OK. Move the equal sign inbetween the quotes :
"[KundenNr] = " &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29])
If KindenNr is a numeric field, this will work, otherwise (a textfield)
you'll have to use quotes :
"[KundenNr] = '" &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29]) & "'"


it doesn't work - access gives me an error message.

Did I understand right:
1 field - what i want to update
Correct (on the form)
2 field - the command for the expr. - where the dates are and then to put
in.
Correct. If you would put "1" there, it would fill the feilld with 1,
if you put a function there, the result of that function will be put
there

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap

thanks again, try it
hope i find the way :-)))


"Gijs Beukenoot" schrieb im Newsbeitrag
...
Na rijp beraad schreef peter junker :
Hello Gijs,
thanks again, but it doesnt work - dont understand why. !

field: 1st field: i put in!
[Vorname] - ( i have to choose from the table Kunden_tbn or the form? )

The form

exp: 2nd field: i put in !
= DLookUp("[Vorname]";"[kunden_tbn]";"[KundenNr]" = &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29])
( combox field for
coustomer )

Sounds almost OK. Move the equal sign inbetween the quotes :
"[KundenNr] = " &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29])
If KindenNr is a numeric field, this will work, otherwise (a textfield)
you'll have to use quotes :
"[KundenNr] = '" &
Formulare![bestellungen-kundenformular]![Kombinationsfeld29]) & "'"


it doesn't work - access gives me an error message.

Did I understand right:
1 field - what i want to update
Correct (on the form)
2 field - the command for the expr. - where the dates are and then to
put
in.
Correct. If you would put "1" there, it would fill the feilld with 1,
if you put a function there, the result of that function will be put
there

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. snap