Dlookup with Navigation forms (1 Viewer)

[sbrown106]

Hi, I am getting really confused with how to use Dlookup with navigation forms, if its possible at all!


if I have a navigation form with Form1 and Form2 as tabs on the menu , so I want to find what the HospitalID value is based
on the fldPatientID (which is also in Form2)

Form1
fldPatientID,txtPatientID
fldHospitialID, txtHospitalID
in Table1

Form2
fldPatientID,txtPatientID
in Table2

So in Form2 I want to look up fldHospitalID from fldPatientID in Table1

Dlookup(Forms!Form1!txtHospitalID,[Table1],Forms![Form2]![fldPatientID])

so what I am doing wrong with this?

 

[arnelgp]

You must first Read about Navigation Form.
Read the manual before operating it.
Only 1 subform can be Active on the navigationsubform.

you can only Dlookup() on the Active Subform.
the other subforms are Not Loaded.

 

[Minty]

You can't lookup values on forms with any Domain functions (DSum, DLookup etc)
They work on tables or queries. Check the syntax guide in my signature.

Another "Gotcha" is that on a navigation form, only one form is loaded at a time, regardless of what the tabs might make you think.
It's clever but means it works differently to how you might think

 

[Pat Hartman]

In addition to the fact that domain functions operate on tables, the Access navigation form does NOT load any subform except the one that is visible so the visible form is the ONLY form you can refer to on the navigation form. If you need to access more than one subform at a time, you will need to build your own "navigation" form by using a tab control.

 

[sbrown106]

Thanks for that reply,
ok so as a work around at the moment. Ive created a query in SQL that has 2 columns , 1 row. In one column is the value in the navigation subform that gets looked up in the other column is the answer. So when I click on the textbox in the nav subform, this qry is run and I get the answer I need [myanswer].

so in my nav form , under page 'Test' I have a textbox = txtvaluelookeduptextbox, textbox = txtboxanswer and my qry is qryanswer (which contains the answer in [myanswer]- is there a simple way to get that answer into that textbox after the qry has run .
I've tried me!textboxanswer=[qryanswer]![myanswer].

Should I be able to do this? I thought it may be possible to reference the row, col index directly as in Python

 

[Pat Hartman]

Rather than a domain function, why not use a combo box to get the value?

 

[mike60smart]

I find that Navigation Forms are a pain to work with.
Recommend that you create your own Navigation Form from scratch.

 

[sbrown106]

I find that Navigation Forms are a pain to work with.
Recommend that you create your own Navigation Form from scratch.

Thanks Pat I found a work around for that last problem. I have this working now, Ii dont think I'm at the point where I can design a Navigation form from scratch yet , but will have a look when ive finished this project