Default value from unbound table 2

[nq]

I have a combo box on a form which is bound to a table. The "row source type" is a value list and I have typed in the values for "row source".
A "default value" is also set.

I have a need to occasionally change the "default value". The new value can be selected from another table. ie not the control source of the current form. This would seem simple enough but I can not get it to work. I have used syntax like:

=[tblParameters]![variable]
=[tblParameters].[variable]

but nothing seems to work. Can anyone help me with the syntax?

 

[Remou]

You need another equals:

[tt]= "=" & [tblParameters]![variable][/tt]

So you end up with

[tt]= 1[/tt]

You will need quotes, Chr(34) is useful, if [variable] is text.

 

[TheAceMan1]

How are ya nq . . .

Are you viewing the table and then selecting a record? . . .if not specify . . .

and how about form/table/field/combobox names!

See Ya! . . . . . .

 

[PHV]

Perhaps the DLookUp function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nq]

Remou - didn't seem to work. I tried:

="=" & [tblParameters]![campus]

since "campus" is text, I also tried:
="=" & [tblParameters]!["campus"]


Aceman - The form is open displaying a record. Click a button to add a new record - all the fields go blank except those that have default values. The form control is named "campus". The other table is called tblParameters and the variable is called "campus". So I want "campus" from tblParameters to become the default value for the bound combo box called "campus". There is only one record in tblParameters - it holds a few variables that change between different campuses where the database is used. I did this so I don't have to customise each front end.

 

[Remou]

You must have missed my comment about chr(34):

="=" & chr(34) & [tblParameters]![campus] & chr(34)

To test, you will need at little more:

'Test data
MsgBox "Data: " & [tblParameters]![campus]

'Switch between the next two lines to test the default
'statement. You can switch by commenting.
'a single quote marks a comment

Me.[i][Name of Control][/i]. DefaultValue= "=" & chr(34) & "Test Default" & chr(34)

Me.[i][Name of Control][/i]. DefaultValue= "=" & chr(34) & [tblParameters]![campus] & chr(34)

 

[Remou]

Oops, I missed PHVs post:

[tt]Me.[Name of Control]. DefaultValue= "=" & chr(34) & DlookUp("tblParameters","campus") & chr(34)[/tt]

Or

[tt]Me.[Name of Control] = DlookUp("tblParameters","campus")[/tt]

 

[PHV]

I'd use this instead:
DlookUp("campus", "tblParameters")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

 

[nq]

Guys.
Thanks for all the feedback. It has given me a lot to think about. I could not get the following (or similar) code to work on the default value:

="=" & chr(34) & [tblParameters]![campus] & chr(34)

However, I will analyse it and see what I need to learn to make it work.

The good news is that the DLookup function did work. I should have thought about it before but I thought the logical place to start was with the "default value" in the control properties.

The code that I finally used was on the Form_Current() event

Me.[Campus].DefaultValue = "=" & Chr(34) & DLookup("[campus]", "tblParameters") & Chr(34)