Need table to update field 1

[4304]

Will anybody help me? I have a field in one of my forms whose information needs to come from one of the fields in the same form. If the enry in the one form is a certain number, I need the other number to automatically populate the other field. It something like this:

"If the 'MR Suffix' in the 'PO Log Form' is the same as the 'MR Suffix' in the 'Material Category Codes and Lookup' table, then the 'Mat'l Cat Code' in 'PO Log Form' is the 'Material Category Code' that corresponds with the 'MR Suffix' record in 'Material Category and Lookup' table.

Any help would be much appreciated. Thanks!

 

[BoxHead]

Take a look at DLookUp in Help.

Something like

=DLookUp("[MaterialCategroyCode]","MCCLtable","[MRsuffix] = " & """" & [txtMrSuffix] & """")

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

Thank you BoxHead! You made it to where my box at least says something. I'm now getting a message that reads #Error. I built the following expression, going by your example. Can you tell me what I am doing wrong. Thanks again!

=DLookUp("[Mat'l Cat Code]","tblMaterial Category Codes and Lookup","[MR Suffix]=tblMaterial Category Codes and Lookup![MR Suffix]")

 

[BoxHead]

A couple of things:

If there's anything you can do to get the apostrophe out of the field name [Mat'l Cat Code], it would be a good idea.

Since you have spaces in your table name, I think it may need brackets around it.

The criteria portion of the DLookUp should be used to relate the table in which you are looking to the Form or report control.

..., "[field_in_my_table] = " & """" & [control_on_my_form] & """")

The extra quotes are used to let Access know that the control on the form is a reference to the value in that control.

Try

=DLookUp("[Mat'l Cat Code]","[tblMaterial Category Codes and Lookup]","[MR Suffix]= " & """" & [theNameOfYourTextbox] & """")

Replace theNameOfYourTextbox with your control name and let me know if that works.



John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

Thanks again John for helping me. I changed the text box name from Mat'l Cat Code to Material Category Code to get rid of the apostrophe, and entered the following:

=DLookUp("[Material Category Code]","[tblMaterial Category Codes and Lookup]","[MR Suffix]= " & """" & [Material Category Code] & """")

This is giving me the #Name? error. Should this be a combo or list box?

Thanks again for your troubleshooting assistance.

 

[BoxHead]

4304,

Let's check soome basics.

You have a table named: tblMaterial Category Codes and Lookup

That table has a field named: Material Category Code

That table also has a field named: MR Suffix

Your form has a textbox that is named: Material Category Code

The control source for your text box is the DLookup statement including the leading equal sign (=)

Are all of these true? The only way that I can get the #Name? error is to leave off the equal sign or mistype Dlookup.



John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

All of the above is true. I did mispell Category in my textbox name. I corrected that, and am getting the #Error message. My apologies for that mixup. In addition, the "error checker" is stating I have a circular reference.

Thank you for your patience with me. Of all the times I've created databases, this is the first time I've had to use the dlookup function.

 

[BoxHead]

The #Error message makes a lot more sense to me so this is good news.

Okay. =DLookUp("[Material Category Code]","[tblMaterial Category Codes and Lookup]","[MR Suffix]= " & """" & [Material Category Code] & """")
Let's break apart the DLookUp and make sure that we are saying exactly what we want to say.

=DLookUp(" HEY, ACCESS, WAKE UP!I want you to look at all of the records in my table called [tblMaterial Category Codes and Lookup] and return the first value in my field [Material Category Code] where the value of that record's field [MR Suffix] is equal to a control on my Form named [Material Category Code]


Does [MR Suffix] actually equal [Material Category Code]? That seems odd. SHouldn't [MR Suffix] be compared to the textbox inwhich someone entered the MR Suffix? Getting a circu;ar reference suggest that you've got this statement in the textbox named [Material Category Code].

Let me know.




John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

You are absolutely correct. The MR Suffix is entered in a different text box, and it gives the Material Category Code its' number. Something like this:

MR Suffix Description Material Category Code
02 Valves 04
03 Flanges 99
04 Pipes 16
05 Bolts 27

Okay, if "02" is entered in the "MR Suffix" box, the MCC should automatically pop up as 04. Does this help?

 

[BoxHead]

Try

=DLookUp("[Material Category Code]","[tblMaterial Category Codes and Lookup]","[MR Suffix]= " & """" & [b][the name of textbox where I entered the MR Suffix][/b] & """")

This should be the control source of the MCC textbox.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

Okay, I entered the following:

=DLookUp("[Material Category Code]","[tblMaterial Category Codes and Lookup]","[MR Suffix]= " & """" & [MR Suffix] & """")

And it is giving me the "circular reference" (#Error) error message. I really appreciate you and please, please, please don't give up on me.

Thanks again!

 

[BoxHead]

Try adding a new textbox and set its control source to
=DLookUp("[Material Category Code]","[tblMaterial Category Codes and Lookup]")

It should return the first [Material Category Code] it finds in [tblMaterial Category Codes and Lookup]

Let me know if it does.



John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

It returned the #Error message. Does this mean there's something wrong with my table?

Thanks again!

 

[BoxHead]

Maybe something is wrong with the table name. It's changed siince we began this?

"If the 'MR Suffix' in the 'PO Log Form' is the same as the 'MR Suffix' in the 'Material Category Codes and Lookup' table, then the 'Mat'l Cat Code' in 'PO Log Form' is the 'Material Category Code' that corresponds with the 'MR Suffix' record in 'Material Category and Lookup' table.

Now it has the "tbl" prefix.

Which is it?

John

Use what you have,
Learn what you can,
Create what you need.

 

[4304]

YOU ARE SO AWESOME!!! That was the problem all along. I had the table named tblMaterial Category Codes and Lookup, thinking the "tbl" was telling access that it was a table. Well, I took out the tbl and it is working perfectly! Thank you, Thank you, Thank you, Thank you!

 

[BoxHead]

Glad to help!


John

Use what you have,
Learn what you can,
Create what you need.