Auto-copy data into a field

[Guest]

I've got a form where the user is entering orders. I'd like it to pull the
price for an item from the master stock item table after the stock item is
entered, but still allow the user to change that price if they'd like and
store it in history (the underlying table for this form is the history table).

Any help would be greatly appreciated.

 

[Guest]

On the after update event of the stock item field you can write the code
' Incase the item number is number type use this
Me.[Price Field Name] = Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form])

' Incase the item number is text type use this
Me.[Price Field Name] = Dlookup("[Price]","[master stock item table
Name]","[Item Number] = '" & Me.[Item Number Name In the form] & "'")

 

[Guest]

One more thing, add to the dlookup the NZ function to replace the Null with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)

 

[Guest]

I tried this, but I get an error message that says Access can't find the
Macro Me

One more thing, add to the dlookup the NZ function to replace the Null with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I've got a form where the user is entering orders. I'd like it to pull the
price for an item from the master stock item table after the stock item is
entered, but still allow the user to change that price if they'd like and
store it in history (the underlying table for this form is the history table).

Any help would be greatly appreciated.

 

[Douglas J Steele]

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried this, but I get an error message that says Access can't find the
Macro Me

One more thing, add to the dlookup the NZ function to replace the Null with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I've got a form where the user is entering orders. I'd like it to pull the
price for an item from the master stock item table after the stock item is
entered, but still allow the user to change that price if they'd like and
store it in history (the underlying table for this form is the history table).

Any help would be greatly appreciated.

 

[Guest]

I understood that, I tried the code sample he gave and substituted my own
field and table names in all the appropriate places. The Me. function or
macro seems to be the problem, Access doesn't recognize it, and unfortunately
I'm not familiar with that one at all to be able to tell if there are any
mis-prints. I did type it in exactly as it was posted.

And the fields in question are all numeric

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried this, but I get an error message that says Access can't find the
Macro Me

One more thing, add to the dlookup the NZ function to replace the Null with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I've got a form where the user is entering orders. I'd like it to pull the
price for an item from the master stock item table after the stock item is
entered, but still allow the user to change that price if they'd like and
store it in history (the underlying table for this form is the history table).

Any help would be greatly appreciated.

 

[Guest]

Can you post your code please?
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I understood that, I tried the code sample he gave and substituted my own
field and table names in all the appropriate places. The Me. function or
macro seems to be the problem, Access doesn't recognize it, and unfortunately
I'm not familiar with that one at all to be able to tell if there are any
mis-prints. I did type it in exactly as it was posted.

And the fields in question are all numeric

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried this, but I get an error message that says Access can't find the
Macro Me

:

One more thing, add to the dlookup the NZ function to replace the Null with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I've got a form where the user is entering orders. I'd like it to pull the
price for an item from the master stock item table after the stock item is
entered, but still allow the user to change that price if they'd like and
store it in history (the underlying table for this form is the history table).

Any help would be greatly appreciated.

 

[Guest]

One more thing, i got this error once in a certain form, and it require that
I'll write the code as
Me![FieldName]
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I understood that, I tried the code sample he gave and substituted my own
field and table names in all the appropriate places. The Me. function or
macro seems to be the problem, Access doesn't recognize it, and unfortunately
I'm not familiar with that one at all to be able to tell if there are any
mis-prints. I did type it in exactly as it was posted.

And the fields in question are all numeric

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried this, but I get an error message that says Access can't find the
Macro Me

:

One more thing, add to the dlookup the NZ function to replace the Null with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I've got a form where the user is entering orders. I'd like it to pull the
price for an item from the master stock item table after the stock item is
entered, but still allow the user to change that price if they'd like and
store it in history (the underlying table for this form is the history table).

Any help would be greatly appreciated.

 

[Guest]

Me.[Cost] = DLookUp("[Cost]","[Stock Items]","[Stock #] = '" & Me.[Stock #] &
"'")

Please note - Several of the fields have identical names in different
tables. IE the History Detail table and the Stock Items table both have
fields named [Cost] and [Stock #]

I tried this same code using the exclamation points after Me instead of
periods, no luck.

One more thing, i got this error once in a certain form, and it require that
I'll write the code as
Me![FieldName]
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I understood that, I tried the code sample he gave and substituted my own
field and table names in all the appropriate places. The Me. function or
macro seems to be the problem, Access doesn't recognize it, and unfortunately
I'm not familiar with that one at all to be able to tell if there are any
mis-prints. I did type it in exactly as it was posted.

And the fields in question are all numeric

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried this, but I get an error message that says Access can't find the
Macro Me

:

One more thing, add to the dlookup the NZ function to replace the Null
with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I've got a form where the user is entering orders. I'd like it to
pull the
price for an item from the master stock item table after the stock
item is
entered, but still allow the user to change that price if they'd like
and
store it in history (the underlying table for this form is the history
table).

Any help would be greatly appreciated.

 

[Guest]

When I said to put the code in the after update event of the form, I ment in
the code section and not directly in the line of the property.

When you in the propery, click on the button with the three dots, and select
code, in the code enter the this line.
The reason that I'm asking that is, if you put something directly in the
line, it will look for a macro with this name, and mybe this is why it prompt
you with this message.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Me.[Cost] = DLookUp("[Cost]","[Stock Items]","[Stock #] = '" & Me.[Stock #] &
"'")

Please note - Several of the fields have identical names in different
tables. IE the History Detail table and the Stock Items table both have
fields named [Cost] and [Stock #]

I tried this same code using the exclamation points after Me instead of
periods, no luck.

One more thing, i got this error once in a certain form, and it require that
I'll write the code as
Me![FieldName]
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I understood that, I tried the code sample he gave and substituted my own
field and table names in all the appropriate places. The Me. function or
macro seems to be the problem, Access doesn't recognize it, and unfortunately
I'm not familiar with that one at all to be able to tell if there are any
mis-prints. I did type it in exactly as it was posted.

And the fields in question are all numeric

:

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried this, but I get an error message that says Access can't find the
Macro Me

:

One more thing, add to the dlookup the NZ function to replace the Null
with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I've got a form where the user is entering orders. I'd like it to
pull the
price for an item from the master stock item table after the stock
item is
entered, but still allow the user to change that price if they'd like
and
store it in history (the underlying table for this form is the history
table).

Any help would be greatly appreciated.

 

[Guest]

That did the trick, thank you so much for your help!

When I said to put the code in the after update event of the form, I ment in
the code section and not directly in the line of the property.

When you in the propery, click on the button with the three dots, and select
code, in the code enter the this line.
The reason that I'm asking that is, if you put something directly in the
line, it will look for a macro with this name, and mybe this is why it prompt
you with this message.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Me.[Cost] = DLookUp("[Cost]","[Stock Items]","[Stock #] = '" & Me.[Stock #] &
"'")

Please note - Several of the fields have identical names in different
tables. IE the History Detail table and the Stock Items table both have
fields named [Cost] and [Stock #]

I tried this same code using the exclamation points after Me instead of
periods, no luck.

One more thing, i got this error once in a certain form, and it require that
I'll write the code as
Me![FieldName]
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I understood that, I tried the code sample he gave and substituted my own
field and table names in all the appropriate places. The Me. function or
macro seems to be the problem, Access doesn't recognize it, and unfortunately
I'm not familiar with that one at all to be able to tell if there are any
mis-prints. I did type it in exactly as it was posted.

And the fields in question are all numeric

:

Ofer meant for you to replace

Me.[Price Field Name]

with the actual name of the control on your form that you want the Price to
go into, and

Me.[Item Number Name In the form]

with the actual name of the control on your form that contains the Item
Number Name.

As well, he's assuming that Item Number is a numeric field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried this, but I get an error message that says Access can't find the
Macro Me

:

One more thing, add to the dlookup the NZ function to replace the Null
with
0, incase the record is not found

Me.[Price Field Name] = NZ(Dlookup("[Price]","[master stock item table
Name]","[Item Number] = " & Me.[Item Number Name In the form]),0)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I've got a form where the user is entering orders. I'd like it to
pull the
price for an item from the master stock item table after the stock
item is
entered, but still allow the user to change that price if they'd like
and
store it in history (the underlying table for this form is the history
table).

Any help would be greatly appreciated.