A bit of a pickle...

[Dalagrath]

Ok, so for my training for where I work I am learning how to filter subforms using a form and on top of that, how to use Dlookup's on the subform that correlates to a related table of the table the subform is based upon. So, for example:



Form A has Form B as Subform

Form B needs to have data filtered on what Form A has chosen (through combobox)

On top of this, there needs to be 2 subforms of Form B to compare the different Filters.

---------------------------- The above part I can get on my own

Now here is where everything goes down hill...

Form B is connected to tblOne but some of the data is in a relationship with tblTwo. I need Form B to take the data from tblOne and grab the data from tblTwo using a Dlookup.

Here is the coding I have tried so far:

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " & "'" & [WeaponID].[Value] & "'")

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " & "'" & [WeaponID1].[Value] & "'")

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " & "'" & [WeaponID1] & "'")

----------------------------

I keep getting the ?#Error thing.

[ssanfu]

You don't need to add ".[Value]" as the value property is the default.

But what is the field type for [WeaponID] and [WeaponID1]?

If it is a number (Long), then it doesn't need delimiters.

Try:

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " &  [WeaponID] )

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " &  [WeaponID1] )

[Dalagrath]

Also, the Weapon1ID is on a form, not in the table.

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " & Forms![frmBattleChooser]![Weapon1ID])

Gives me a #Name? Error.

[ssanfu]

Also, the Weapon1ID is on a form, not in the table.

Then you can't use the DLookup() function. If you look up "DLookup" in HELP:

DLookup(expr, domain, [criteria])

expr: An expression that identifies the field whose value you want to return.

domain: A string expression identifying the set of records that constitutes the domain (table/ query name)

[criteria]: An optional string expression used to restrict the range of data on which the DLookup function is performed. criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE.

The left part of the criteria is a field in the domain that is used to filter/select/search for the required records. If the search field is not in the domain (table), how can you search (filter) for it?

[Dalagrath]

Your saying there is no way to get a form to look up data related to the form's control source?

[ssanfu]

Sorry, I totally misread your post. Don't know what I was thinking.....

=DLookUp("[WeaponName]","tblWeapons","[WeaponID] = " & Forms![frmBattleChooser]![Weapon1ID])

The above Dlookup() function should work..