Solved nagivation control. where clause = new record

[TipsyWolf]

hello everyone.
i can't figure it out how to set formula so it opens up a form with new record.

this doesn't work

i have done it so far via on click event

but i dont like it as it opens it up in a seperate window where as navigation form opens it in build in window, but ID=1 set by default.

any suggestions ?

 

[Gasman]

Hardly anyone here uses macros that I know of at least?
Might want to start learning VBA. Much easier and more flexible in my opinion.
Aren't you actually asking it to open a particular form?

 

[Pat Hartman]

but i dont like it as it opens it up in a seperate window where as navigation form opens it in build in window, but ID=1 set by default.

You may not like it but that is the way it works. And it doesn't matter whether you use the OpenForm Action (macro) or OpenForm Method (VBA). The result is a separate form because that is what OpenForm does. It opens a form.

If you like the way a navigation form looks/works (I don't like it so I don't use them), add an item to the navigation menu to open the form in add mode.

 

[TipsyWolf]

Hardly anyone here uses macros that I know of at least?
Might want to start learning VBA. Much easier and more flexible in my opinion.
Aren't you actually asking it to open a particular form?

yeap. it already works fine. navigation control opens a form i already built. but it does it with ID=1.
so is there any way to type something in "navigation where clause" so it does it with new record ?

or i misunderstood the purpose of "navigation where clause" and it meant for something else

 

[Gasman]

2007 does not have navigation forms, so I cannot help there. Sorry.

 

[Pat Hartman]

but it does it with ID=1.

where does the ID come from? If it is coming from a control on the menu, then change the Where clause to:

Where ID = Forms!mymenu!myID OR Forms!mymenu!myID Is Null

 

[TipsyWolf]

where does the ID come from?

it comes from Main Table with Primary Key.
Form has a record source set as this Main Table

it didn't work still starts with ID=1

 

[theDBguy]

Have you tried entering ID=0 in the Navigation Where Clause? Just curious...

 

[Pat Hartman]

it didn't work

What didn't work? You have to show us the query

 

[TipsyWolf]

What didn't work? You have to show us the query

but it has no query. only NavTargetName and NavWhereClause

 

[TipsyWolf]

Have you tried entering ID=0 in the Navigation Where Clause? Just curious...

yas, i tried many variaties like :

Forms!FRiskPage!RiskID is 0
Forms!FRiskPage!RiskID is Null
Forms!FRiskPage!RiskID = 0
Forms!FRiskPage!RiskID = Null
Forms!FRiskPage!RiskID is new()
Forms!FRiskPage!RiskID = new()

and it doesnt want to loat the form with NewRecord

 

[theDBguy]

yas, i tried many variaties like :

Forms!FRiskPage!RiskID is 0
Forms!FRiskPage!RiskID is Null
Forms!FRiskPage!RiskID = 0
Forms!FRiskPage!RiskID = Null
Forms!FRiskPage!RiskID is new()
Forms!FRiskPage!RiskID = new()

and it doesnt want to loat the form with NewRecord

But have you tried with simply? RiskID=0

 

[TipsyWolf]

But have you tried with simply? RiskID=0

just did, but it still does start with ID=1

 

[Pat Hartman]

When you use the OpenForm Action or Method, you can pass in a filter or where clause. There is not option like that when you are using a navigation form. Therefore, if you want to control what the form displays, you need to bind the form to a query and the query needs to have criteria.

I have no idea where you code is or why you think it will filter the form.

I personally NEVER use the navigation form. It works the way Access decided it should work. You can make your own version of the form that looks like the built in one but acts the way you want it to act. You can still use a single subform control and only load ONE form at a time ever, which is the way the navigation form works.

An option which I mentioned in passing, I will bring up again. If you only ever want the form to open to a new record, then change the properties of the form so that it always opens in add mode.

Your best, most flexible option is to use criteria in the form's RecordSource query as I also suggested as I mentioned 6 posts ago.

Or, you can wait until someone who uses this type of form sees the thread and gives you a different solution. Apparently theDBGuy doesn't use them either and Gasman can't so we are at an impasse here.

1. Do you ALWAYS, ONLY want the form to open to a new record --- change the properties of the form
2. If you want flexibility to open to a new record or a specific record --- use the technique I mentioned of having the Where clause of your RecordSource query reference a control on the main form that provides the ID of the record you want the form to open to.

 

[Minty]

Although long winded and possibly unnecessary if the other suggestions work, you could use OpenArgs in the form open options, to Pass in a string "New" when opening the form, and use that in the forms load event to go to a New Record

 

[theDBguy]

just did, but it still does start with ID=1

I gave it a try, and it worked fine for me. Can you post a sample db to demonstrate the problem?

 

[TipsyWolf]

I gave it a try, and it worked fine for me. Can you post a sample db to demonstrate the problem?

sure. here it is. original.

u need FmainMenu. 2nd botton

 

[mike60smart]

See if the attached works for you.

I put the following in The On Click Event of the Button

Private Sub NavigationButton186_Click()

10        On Error GoTo NavigationButton186_Click_Error
20    DoCmd.OpenForm "FRiskPage"
30    DoCmd.GoToRecord , , acNewRec
          
40        On Error GoTo 0
50        Exit Sub

NavigationButton186_Click_Error:

60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure NavigationButton186_Click, line " & Erl & "."

End Sub

 

[TipsyWolf]

See if the attached works for you.

I put the following in The On Click Event of the Button

Private Sub NavigationButton186_Click()

10        On Error GoTo NavigationButton186_Click_Error
20    DoCmd.OpenForm "FRiskPage"
30    DoCmd.GoToRecord , , acNewRec
         
40        On Error GoTo 0
50        Exit Sub

NavigationButton186_Click_Error:

60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure NavigationButton186_Click, line " & Erl & "."

End Sub

it does work !
it opens as a seperate window, but i think its not possbile to make it in built it nav. control window
thanks a lot @mike60smart ! u save my day !

 

[theDBguy]

sure. here it is. original.

u need FmainMenu. 2nd botton
View attachment 113474

Hi. I entered RiskID=0, and it worked for me. I'll send an image or reupload your db later.

Sent from phone...