Form look up

[grego9]

I have a table in my database called CPARTY that lists all the business units in my company. This table also has a field called short code.

I have a form that I want to use to enter data into a table called BU.

The form has a field called BU that pulls in the complete list of business Units from the CPARTY table

I have another field on the form called Short code. I want the form to automatically pull in the short code from the CPARTY table using the business unit name entered on the form to locate the relevant short code from the CPARTY table

Does anyone know how to do this?

thanks for your help

 

[Galaxiom]

DLookUp("[ShortCode]","[CPARTY]", "[CPARTY].[BU]=Forms![BU]![BU]")

BTW I would avoid calling a field the same name as a table.

 

[grego9]

Do I enter this formula in the control source part of the text box properties on the form?

thanks for your help

 

[grego9]

I put this in the control source section of the "Short Code" text box on the form.

=DLookUp("

","[QTLIVE]![CPARTY]","[QTLIVE]![CPARTY].[NAME]=Forms![BU]![Business Unit]")
 
CODE is the code I want the form to show.  This is in a linked table that appears as QTLIVE_CPARTY.  The link between the form I am working in(called BU) and the QTLIVE_CPARTY table is the field in the BU form called "Business Unit" should equal the "NAME" field in the QTLIVE_CPARTY table
 
I just get an #error message returned - but I am not sure why!

 

[Galaxiom]

=DLookUp("

","[QTLIVE]![CPARTY]","[QTLIVE]![CPARTY].[NAME]=Forms![BU]![Business Unit]") [/quote]
 
I have never seen the database name included in a reference in this way and I would doubt that it is valid.
 
Try this:
[code] =DLookUp("code","[CPARTY]","[CPARTY].[NAME]=Forms![BU]![Business Unit]")

 

[grego9]

I have created a query called CPARTY2 with the relevant details in it and referenced this in the form - so the formula now looks like this:

=DLookUp("CODE","[CPARTY2]","[CPARTY2].[NAME]=Forms![BU]![Business Unit]")

The #error message on the form has gone - but the form is not automatically pulling through the code - it is just blank. Any ideas?

As the BU Form is based off the BU table - do I need to do anything to the settings against Code within the BU Table for it to update when the BU name changes?

thanks again

 

[Galaxiom]

Can't quite get my head around your situation but here are the underlying principles which I think will help you work it out.

Bound forms have a Record Source. This can be a query or a table.
The fields in the record source are connected to the controls on the form through their Control Source. Just enter the simple field name in the control source property.

The form wizard will name the controls the same as the fields in the record source and this can lead to confusion, pariticularly when names are edited. Some developers avoid naming controls to match the fields to avoid the potential problems.

When referring to a textbox control on a form the full terminology is:
Forms!formname.Form!controlname.Value

However the default property of the form is .Form and .Value is the default property of the control so it is frequently shortened to:
Forms!formname!controlname

However if a control by that name is is not found in the controls collection of the form, Access will look at the Recordset of the form for a field by that name.

Consequently Forms!formname!somename can also find:
Forms!formname.RecordSet!fieldname

Hope this helps.

 

[grego9]

I think I may be over complicating this so thanks for sticking with me.

The Form I was using (called BU) was to post data to a table also called BU.

I want the the Code field that exists in the table to automatically populate once the BU name is selected. So I think I may actually need the lookup in the table rather than the form - is this correct?

I went to the table design view and I can link in the relevant data from the other table by doing a look up wizard agains the field data type- but it displays as a combo box letting me manually select the code rather than automatically finding the right record and recording the relevant code in the table

Thanks for your continued help on this!

 

[Galaxiom]

The code field needs to be bound to the record source. Ensure the name of the appropriate field from the record source in the control source of the text box. The text box is a reflection of the contents of the table and vice versa. Change the table and the text box follows. Change the textbox and the table follows.

Now I have a better idea of what you are doing I wouldn't use the Dlookup.

In the combobox Row Source include both the business unit name and the code in that order. Change the column count to two. Set the bound column to 1 (the name).

In the After Update event of the combo:
textboxname = comboboxname.Column(1)

This will load the selected value in the second column (index 1 because they start counting at zero) into the text box.

Sorry I didn't understand your first description or we could have sorted this sooner.

 

[grego9]

Apologies - this didn't make much sense to me. I just want to clarify then when I said that the code value is the table - it is in another query [CPARTY2] and not in the BU Table that I want my BU Form to post information to. That's why I thought DLOOKUP would be best.

Does your solution still apply? and if so you refer to a combobox. Currently the item on my form is a TextBox as is the field in the table. Do you want me to change these to a Combobox?

thanks for your patience!

 

[Galaxiom]

No need to change the table. They are best left as simple fields rather than Lookups. You can still write values from a combobox into a field like this.

A combobox's can do essentially the same job as a Dlookup in this situation. It translates one value to another. Morover it will work without modification with data from a SQL server.

Data in CPARTY2 is used as the Row Source.

 

[grego9]

I don't think I'm explaining this very well - I still can't get it to work.

Let me try to simplify it.

I have a table called BU where I want all my data to end up.

I've created a Form called BU where the user can enter data.

The first field the user selects is a combo box that has a list of Business Unit names in it. This list of names is pulled in from a query called CPARTY2

When the Business Unit name is selected I want a field on my form to be populated with the internal code that relates to that Business Unit. This code is also in the CPARTY2 table.
(I then subsequently want this code posting into the BU table to report off)

In your last solution I didn't see how the code would be pulled from the CPARTY2 table.
Hope you can stick with me on this one!