addressing subform

[Bladerunner]

Yes it's a control and you can interact with it. You have added layers of controls and subforms when you only have one table.

Not sure I understand but yes only one main-type table. 3 (supply) tables. Type, Group and Location. All pull up data on main table where the Combo cboanimalID (connected- unbound) to [tblAnimal Setup]. The subform---[frmanimalsetup] is the first of many but have to make sure this part works first.

I just want the Combo [cboanimalID] on the main form to display the picked record from [tbanimal setup] on the [frmanimalsetup] in the navigationsubform. I can install all the cmd buttons I need to go from there but it appears I have to go around the moon just to get to that point using the preset.Navigation Forms. I think it will be worth it.

Thanks

Blade

 

[Bladerunner]

Am trying to get record but get a 3085 error code on the 'defaultcboAnimalID.Column(0)' for some reason.. In the immediate window, this control gives me the right record ID just stops?

Private Sub defaultcboAnimalID_AfterUpdate()

Me.NavigationSubform.Form.RecordSource = "SELECT * FROM [tblAnimal Setup] WHERE [tblAnimal Setup].AnimalSetupID = [defaultcboAnimalID].Column(0) "

End Sub

This should just simply pull up the record and display it?

p.s. I have used 'me.defaultcboanimalID.column(0) with the same error code.' This control still has focus so 'me.' should not be needed?

 

[Galaxiom]

The Column collection cannot be used directly in SQL. You must use the Value property which is determined by the Bound Column.

Alternatively, concatenate the value into the string.

Assuming a numeric value:

Me.NavigationSubform.Form.RecordSource = "SELECT * FROM [tblAnimal Setup] WHERE [tblAnimal Setup].AnimalSetupID = " & Me.[defaultcboAnimalID].Column(0)

 

[Bladerunner]

This problem has been solved! Have gone to the moon and back but got it! Below is the syntax for a Navigation Form with a Combo Box that picks the record for the subform to display.

I used in code section the following:

Me.NavigationSubform.Form.RecordSource = "SELECT * FROM [tblAnimal Setup] WHERE [tblanimal setup].animalsetupID = defaultcboAnimalID"

me.'your subform's container name'.Form.Recordsource = "Select * From 'your table' Where 'your tables primary recordID' = 'your combobox control name that is on the main Navigation form'

I want to thank everyone who has helped me get this far.

Blade

 

[Bladerunner]

The Column collection cannot be used directly in SQL. You must use the Value property which is determined by the Bound Column.

Alternatively, concatenate the value into the string.

Assuming a numeric value:

Me.NavigationSubform.Form.RecordSource = "SELECT * FROM [tblAnimal Setup] WHERE [tblAnimal Setup].AnimalSetupID = " & Me.[defaultcboAnimalID].Column(0)

Yes, you are right a regular main/subform configuration however, the Navigation Form will not permit any type of Column() on either side of the equation. It seems you are still in focus on the control of the main form thus, ('ME.' ) is not necessary for the [defaultcboAnimalID]. Also the ('&') character is not needed so you wind up with the following.

 Me.NavigationSubform.Form.RecordSource = "SELECT * FROM [tblAnimal  Setup] WHERE [tblAnimal Setup].AnimalSetupID = [defaultcboAnimalID]"

Again , Thanks for your help.
Blade

 

[Galaxiom]

It seems you are still in focus on the control of the main form thus, ('ME.' ) is not necessary for the [defaultcboAnimalID]. Also the ('&') character is not needed so you wind up with the following.

 Me.NavigationSubform.Form.RecordSource = "SELECT * FROM [tblAnimal  Setup] WHERE [tblAnimal Setup].AnimalSetupID = [defaultcboAnimalID]"

It isn't about the focus. Where a control reference is passed directly as part of the recordsource the reference is not ambiguous. It knows the object is on the form.

The expression with the "&" and the reference to the Column is quite different. In this case the Column can be referred to as Me.control.Column because the reference is within VBA itself.

The ampersand concatenates the value returned by this expression onto the SQL string.

It should include the Me when it is in VBA because Me refers to the current context object.

Without the Me, VBA will first check for a variable by that name then look in the context object when the variable is not found.

 

[Bladerunner]

It isn't about the focus. Where a control reference is passed directly as part of the recordsource the reference is not ambiguous. It knows the object is on the form.

The expression with the "&" and the reference to the Column is quite different. In this case the Column can be referred to as Me.control.Column because the reference is within VBA itself.

The ampersand concatenates the value returned by this expression onto the SQL string.

It should include the Me when it is in VBA because Me refers to the current context object.

Without the Me, VBA will first check for a variable by that name then look in the context object when the variable is not found.

Thanks Galaxiom: I went back on this program and added the column parameter. It gave e a error code of 2465 "cannot find field '|1' referred to in your expression"

I removed it and kept the me.controlname. It ask me to enter a parameter value. It could not find the value of the control.

I then removed the "me." and it worked perfectly. I did not try the ampersand as I did not see a need to concatenate anything.

Now to what you said is perfectly correct in VBA. However, in this this case of the navigation form/subform, the normal VBA code seems to be put aside. I can only surmise that it is because of the fact that the navigationsubform is itself a control. No really sure, but it does work where other variances do not.

Thank you

Blade.

 

[Galaxiom]

Thanks Galaxiom: I went back on this program and added the column parameter. It gave e a error code of 2465 "cannot find field '|1' referred to in your expression"

Yes because the direct reference in the SQL has no comprehension of the combo's Column collection.

I removed it and kept the me.controlname. It ask me to enter a parameter value. It could not find the value of the control.

The direct reference in the SQL has no concept of Me because Me is VBA.

I then removed the "me." and it worked perfectly. I did not try the ampersand as I did not see a need to concatenate anything.

The concatenation is only required if you want to refer to a column other than the Bound column (via the Value property). The concatenation allows the reference to the column because that reference is resolved in VBA and the value returned concatenated onto the SQL string. Hence the SQL never sees the Column reference.

Now to what you said is perfectly correct in VBA. However, in this this case of the navigation form/subform, the normal VBA code seems to be put aside. I can only surmise that it is because of the fact that the navigationsubform is itself a control. No really sure, but it does work where other variances do not.

All subforms are controls. The form object displayed in the subformcontrol is designated by the SourceObject property of the control.

From what I can see, the Navigation control uses Switchboard forms. Switchboards are ridiculously complex objects, crippled by using their RecordSource to hold information about the database structure instead of real data.

I see them as pointless and I would never use them.

 

[AccessBlaster]

Just to be clear what Blade means by...

navigation form/subform

https://www.youtube.com/watch?v=ovcxmeyrILQ

 

[Bladerunner]

Thanks AccessBlaster. That is far more technical than I could be and I hope it helps someone else out there using this navigationform/subform config. These post have certainly guided me in using (as you say) these complex forms).

I will ask that since you never use this navigation configuration, is there alternative out there that would allow you to use the Tabs as this does beside the switchboard. I like the idea that when I turn on the program a specific form is showing on screen ready to go with all the others forms a click away without having to switch back and forth. I would also like to use the menu bar (file, edit, User defined, etc...) that is on most programs and is easily setup in VBA. Does Access have something like that available?

Thanks again.

Blade.

 

[AccessBlaster]

Have you looked at the tab control under the design tab? More people are familiar with it. Also here is a link to help with referring to objects / controls on a form. http://access.mvps.org/access/forms/frm0031.htm

 

[Bladerunner]

This is directed at the 'Group', and 'location' search cbo's on the navform.
Detail:

tblgroup will now include *NEW* and *ALL*......If the user chooses *NEW* the group form will immediately show up to accept a new category. This I can do later.

The detail I am most interested in is the *ALL* category. if this category is picked, all groups in the 'tblanimalsetup' for that field will be considered. If I use this category in the 'location' cbo it would bring a lot of additional power to the program.

Example: The cboGROUP selection is 'Nursery' and my 'cboLocation' = *ALL*
meaning, an animal in the nursery(nursing a little one) is in an unknown location or (field). This could be used visa-versa with Location = 'sink-hole-field' and the group is unknown. The *ALL* would include " " fields as well.

Can I put these search parameters inside of the SQL and if so please give me an example or should I endeavor to use VBA code ?

Thanks
Happy Holidays to ALL.

Blade