Sub form not working in a navigation form (1 Viewer)

MAAS8000

Registered User.
Local time
Today, 02:32
Joined
Apr 3, 2018
Messages
38
I have a form contain 4 combo boxes used to filter a query
it all working well
when I used this form as a sub form in a navigation form and try to run the query, access request the value of the parameter
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2002
Messages
42,981
When you make a form a subform, the way you reference it changes.

Original:
Forms!yourform!yourcontrol

New:
Forms!yourmainform!yourform!yourcontrol

Intellisense in the query will help you to build the reference.
 

MAAS8000

Registered User.
Local time
Today, 02:32
Joined
Apr 3, 2018
Messages
38
my form is "FrmOldTransaction"
navigation form is "Dash board"

original code : Like [Forms]![FrmOldTransaction]![CboPatientData] & "*"

edited code: Like [Forms]![Dash Board]![FrmOldTransaction]![CboPatientData] & "*"

also not working
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2002
Messages
42,981
Navigation forms load only ONE subform at a time so it is NOT possible to reference sfrmA from sfrmB. Is that what you are trying to do?
 

JHB

Have been here a while
Local time
Today, 10:32
Joined
Jun 17, 2012
Messages
7,732
Post your database with some sample data, (do a "Compact & Repair" + zip it).
 

MAAS8000

Registered User.
Local time
Today, 02:32
Joined
Apr 3, 2018
Messages
38
my form called : FrmOldTransaction
it also in the navigation form : dash board

the button : retrieve old transaction work in the form only not in the dash board
it need me to enter the parameter
 

Attachments

  • sample.zip
    194.9 KB · Views: 58

JHB

Have been here a while
Local time
Today, 10:32
Joined
Jun 17, 2012
Messages
7,732
Try it now:
 

Attachments

  • Database56.accdb
    1.2 MB · Views: 57

MAAS8000

Registered User.
Local time
Today, 02:32
Joined
Apr 3, 2018
Messages
38
thank you it, working now
Q: I have three parameters in the query to be filtered by the combo box
the last one is the transaction date
i could not make it like &"*" to avoid if the combo box is blank
can you help me


Between [Forms]![Dash Board]![NavigationSubform]![CboFromDate] And [Forms]![Dash Board]![NavigationSubform]![CboUpToDate]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2002
Messages
42,981
@JHB,
It isn't really helpful when you make a change but do not explain what you did.

@MAAS8000,
LIKE is a string operator. Dates are numeric values. They are stored as double precision numbers with the integer being the number of days since Dec 30, 1899 and the decimal being the fraction of the day since midnight.

The best solution when your criteria is a date range is to have your form code ensure that both dates are populated or filled with a default if they should be open ended.
 

JHB

Have been here a while
Local time
Today, 10:32
Joined
Jun 17, 2012
Messages
7,732
..Between [Forms]![Dash Board]![NavigationSubform]![CboFromDate] And [Forms]![Dash Board]![NavigationSubform]![CboUpToDate]
You could fake it a little:
Between Nz([Forms]![Dash Board]![NavigationSubform]![CboFromDate];0) And Nz([Forms]![Dash Board]![NavigationSubform]![CboUpToDate];#01-01-3100#)
 

MAAS8000

Registered User.
Local time
Today, 02:32
Joined
Apr 3, 2018
Messages
38
Good,but I faced a new problem :(
for the transaction number I made this

Like [Forms]![Dash Board]![NavigationSubform]![CboTransNum] & "*"

so when the combo box is blank it retrieve the all transaction
the problem that when I request transaction number 1
it gives me 1 , 11, 12, 13, etc
so i tried two ways but both didn't retrieve any thing when combo box is blank


IIf([Forms]![Dash Board]![NavigationSubform]![CboTransNum] Is Null,"",[Forms]![Dash Board]![NavigationSubform]![CboTransNum])

N.B. i tried to put star "*" also not work
Nz([Forms]![Dash Board]![NavigationSubform]![CboTransNum],"")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Feb 19, 2002
Messages
42,981
Like is a string operation so it isn't going to work the way you expect it to when working with numbers or dates.

Try
WHERE TransNum = [Forms]![Dash Board]![NavigationSubform]![CboTransNum] OR [Forms]![Dash Board]![NavigationSubform]![CboTransNum] Is Null
 

Users who are viewing this thread

Top Bottom