Combo box that fills in 2 fields (1 Viewer)

[smeagain]

Hi

I have a form which has a combo box that lets you choose a record which then populates a field, how can I make it so that when you choose the record from the drop down box it also populates a second field with a second record from the same table as the first.

 

[smeagain]

I will try and expain a bit better. On my form I have a combo box that lets you choose a billing option either, Daily, Weekly or Monthly. When you chose the option that you want, I want the form to look at my table called "expense codes" and depending on the option that is chosen then enter the appropriate fee from the coresponding fee field in "expense codes" table into a field called costs on the form.

 

[vbaInet]

Let us see a legible screenshot of your Relationships diagram and one of your form (indicating the combo box and the textbox).

 

[smeagain]

Here you go.

 

[John Big Booty]

Have a look at the DlookUp() function.

 

[smeagain]

I am no tsure how this should look, I have tried - DLookup("[Expense Code]", "Expense Code", "Fee"). I know this inot correct ad does not work. [Expense Code] is the Field name & "Expense Code" is the Table name, "Fee" is the name of the field that I want to appear in the Cost box on the Fome,but I have no idea how the DLookup string should look. I have attached picture of how my form looks

 

[John Big Booty]

The DlookUp() should be structured something like;

DlookUp("FieldNameToReturn", "TableToLookIn", "RecordID = " & Me.ComboName

 

[smeagain]

Hi, Thanks for all of your help, but I am totally lost with this I have tried- = DlookUp("Fee", "Expense Code", "RecordID = Cost" & Me.ComboName. But it does not work

 

[John Big Booty]

You will need to change the highlighted portions of the code to match the control in your DB;

= DlookUp("Fee", "Expense Code", "[B][COLOR="Red"]RecordID[/COLOR][/B] = " & Me.[COLOR="Red"][B]ComboName[/B][/COLOR])

RecordID should be the ID that is in the bound field of your combo box, whilst ComboName should be the name of your combo box. Additionally I had inadvertently omitted the right parenthesise

You might also want to consider a naming protocol for your DB objects and controls, something along the lines of; TBL_TableName, FRM_FormName, QRY_QueryName etc. Also avoid using spaces and other special characters in names, limit yourself to alpha and numeric characters and the underscore (_)

 

[smeagain]

Hi, This is what I am now trying - =DLookUp("Fee","Expense Code","Cost = " & [Me].[ex code]), but am getting an error, have a look at the picture of the error attached. Basically I have the following "Fee" is the Field which is in the "Expense Code" table that I want to be displayed in the "Cost" field on my form when I choose an option from my combo box called "ex code". I am so confused

 

[vbaInet]

Just remove [Me].

 

[smeagain]

Hi, I have tried that, e.g. =DLookUp("Fee","Expense Code","Cost =" & "ex code") and still get an error, plese see the attached picture

 

[vbaInet]

When you remove [Me]. from this -->

=DLookUp("Fee","Expense Code","Cost = " & [Me].[ex code])

Is it the same thing as this? -->

=DLookUp("Fee","Expense Code","Cost =" & "ex code")

 

[smeagain]

Hi, I am totally lost now, none of the suggestions work. I have tried everything but do not have a clue.

 

[John Big Booty]

Perhaps you can post a copy of your DB (pre 07 if poss)

 

[smeagain]

HI, My database is 15MB and the max attachment size is 2mb so I am stuck sorry. I have attached a picture of my form, as you will see there are 2 fields Cost and ex code. What I want to happen is when you choose an item from the combo box(ex code) for example weekly, it then enters the correspondng cost into the Cost field.

 

[Kryst51]

HI, My database is 15MB and the max attachment size is 2mb so I am stuck sorry. I have attached a picture of my form, as you will see there are 2 fields Cost and ex code. What I want to happen is when you choose an item from the combo box(ex code) for example weekly, it then enters the correspondng cost into the Cost field.

I am not positive but maybe what vbaInet was trying to get at is that when you took off the "Me." you also changed the rest of it. Try what the following as per vbaInet's suggestion (I think ):

=DLookUp("Fee","Expense Code","Cost =" & [ex code])

EDIT: Also to atach your db, compact and repair, then zip it up, you should then be able to attach it.

 

[vbaInet]

I am not positive but maybe what vbaInet was trying to get at is that when you took off the "Me." you also changed the rest of it. Try what the following as per vbaInet's suggestion (I think )

Spot on Kryst51

And here's a good link on DLookup() syntax:

http://www.mvps.org/access/general/gen0018.htm

EDIT: Also to atach your db, compact and repair, then zip it up, you should then be able to attach it.

How to attach your db -->

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[John Big Booty]

HI, My database is 15MB and the max attachment size is 2mb so I am stuck sorry. I have attached a picture of my form, as you will see there are 2 fields Cost and ex code. What I want to happen is when you choose an item from the combo box(ex code) for example weekly, it then enters the correspondng cost into the Cost field.

Remove as much superfluous data and structure as possible, and do a compact and repair, you should be able to get you DB down to a manageable size.

 

[smeagain]

Hi, I did try that but got another error which I have attached. I have also attached a copy of the database.The database was created using a tempate which I am tailoring.