variable cbo?

[Bladerunner]

Have 3 CBO location, group and names. Names I based upon the other two cbos. Instead of writing a query for each possibility how or can I use variables so only one or two queries can be written. Ex. Location.... Front house. Group...active there are five animals that are housed here and will be in the names cbo.

In VBA I could do this but do not how to in access

Thanks for the help in advance

 

[Mile-O]

I think you are going to have to take a step back, think about what you are trying to do, and write a more detailed post. None of what you have said us very clear and, having read it over multiple times, I am still unsure as to what you are trying to do.

You have three cbo (meaning comboboxes) and each one contains a list of locations, groups, and names. Locations, groups, and names of what? It's a bit abstract when we don't know the purpose of your database. And the word 'animals' is just thrown in there.

I suspect you may mean you are trying to do some form of cascading comboboxes, with Names somehow dependent on Locations and Groups, but there's no context to your post. Or are we having a regular cascade where the user selects Locations, this then filters the Groups combobox to all those groups in that one location, and then selecting from the filtered Groups, narrows the Names combobox down to all those names related to that Group.

 

[Bladerunner]

OK will try again. Have an animal database. Main table = [tblanimal setup],
This main table contains locations, group and of course, 'Animal ID' of each animal among other info. There is one tblLocation and one tblGroup that allows the user to pull them down on locationcbo, groupcbo located of the [navigation form](in the header area).

On a [navigation form], at the top, I have three cbos. one for location, one for group and one for the animal ID. Keep in mind these are not cascading. locationcbo and groupcbo can be empty. (default, null = All). p.s. (default, null in [animalDcbo] chooses no IDs thus any forms pulled up will be empty)

what I am trying to do is pull up the animals IDs in the third (animalIDcbo), by using a query that looks possibly at the 'afterchange' value of Location and Group. It then looks to the main table [tblanimal setup], pulls up the animals that are in those locations and groups (does not matter what order). After the animal has been chosen, that (number or name) will be used to pull up data in various other forms (one at a time) on the navigation form. An Example, (IF [locationcbo] ="main pasture"', [Groupcbo] ="Nursery" then [AnimalIDcbo] will list 12 animals in the [animalIDcbo] drop down box.Now lets say the chosen [animal ID] from [AnimalIDcbo] = (5784B). The Health tab on [navigation form] is clicked and the last information of the healthrecord for (5784B) appears on the Health Form (captured from [tblhealthrecords]). At this point new info can be added to this table via new healthrecord.

Just cannot get it to give me the animalID in the third combo. Should be able to use one maybe two queries since they are not cascading. have tried using: [forms]![Navigation Form]![groupcbo] in the query design-view to pull up the IDs. (testing only on groups but location should be close to the same.)

Trying to keep the number of queries down here.

The combinations would be to staggering to write a query for each. I have 14 locations and there are about 6-8 groups and that is small to what some people will ave.

Everything else is almost done except making it pretty. I had been using a switch board. It does work but when I pull up inventory, it pulls up 'all' animals. Not acceptable.

Hope this helps. No much on language of Access.

 

[Bladerunner]

Would it be better to treat these as cascading combos even though they are not by the rules?

As Location does not have any effect on Group or vice-versa. Only on the 3rd combo box [AnimalIDcbo]


Thanks again.

 

[Bladerunner]

OK, got it this far using the query in the design.form. It works by using :
[Forms]![FormNavigation]![cboType]
[Forms]![FormNavigation]![cboLocation]
[Forms]![FormNavigation]![cboGroup]
and of course answering the parameter boxes.

However, it does not work using the form. In fact it stops and appears to go into a loop. I opened the query 'OnGOTFocus' of the AnimalID. Each of the other combos use a row source. Animal ID has no row source.

Here is the SQL:

SELECT [tblAnimal Type].[Animal Type], tblLocation.Location, tblGroup.Groups, [tblAnimal Setup].[Animal ID]
FROM tblLocation INNER JOIN (tblGroup INNER JOIN ([tblAnimal Setup] INNER JOIN [tblAnimal Type] ON [tblAnimal Setup].[Animal Type] = [tblAnimal type].
[Animal Type]) ON tblGroup.ID = [tblAnimal Setup].Group) ON tblLocation.[Location ID] = [tblAnimal Setup].Location
WHERE ((([tblAnimal Type].[Animal Type])=[forms]![Navigation Form].[cbotype]) AND ((tblLocation.Location)=[Forms]![Navigation Form].[cbolocation]) AND
((tblGroup.Groups)=[Forms]![Navigation Form].[cbogroup]));

been at this for hours. think I will take a break!

Thanks for any help.

 

[Mihail]

Hi, Bladerunner.
I think that the best bet is to upload the DB (Access 2003 version - I use 2007) with some data for tests.

At the first view, the answer seems to be a combination between VBA (external function(s) and querie(s)).

 

[Bladerunner]

Here is a shorten version of the db but with all the working parts for this thread.

Hi Mihail: Thanks for the help.

 

[Mihail]

Unfortunately I can't open your DB. Seems to be in a newer version of Access.
Go to Office button then Save As... 2003

 

[Bladerunner]

Unfortunately I can't open your DB. Seems to be in a newer version of Access.
Go to Office button then Save As... 2003

Mihail: the 2003 model does not navigation pane the way my 2010 has. It does not matter. the top four combo boxes are the problem. Hope this helps.

Have a nice day:>)
Bladerunner

 

[Mihail]

Mihail: the 2003 model does not navigation pane the way my 2010 has. It does not matter. the top four combo boxes are the problem.

Is the same (or very similar). Open the Navigation Pane and check the "Show all Access objects" (Hope I remember well ).

OK. Now I'll take a look to your DB and I'll see if I am able to help you.

 

[Mihail]

Is this what you are looking for ?

 

[Bladerunner]

Is this what you are looking for ?

May be doing this wrong but it did not show up anything. it should give a list of animal id to choose from. Yes, I opened the downloaded file.

have a nice day :>)

Bladerunner

 

[Mihail]

Ups. Sorry for this.
If you switch to design view the, again, to form view, will work .

Replace the VBA code with this:

Private Sub cboGroup_AfterUpdate()
    If Not IsNull(Me.cboLocation) Then
        Me.cboAnimalID.Requery
    End If
End Sub

Private Sub cboLocation_AfterUpdate()
    If Not IsNull(Me.cboGroup) Then
        Me.cboAnimalID.Requery
    End If
End Sub

and hope to be OK.

 

[Bladerunner]

Ups. Sorry for this.
If you switch to design view the, again, to form view, will work .

Replace the VBA code with this:

Private Sub cboGroup_AfterUpdate()
    If Not IsNull(Me.cboLocation) Then
        Me.cboAnimalID.Requery
    End If
End Sub

Private Sub cboLocation_AfterUpdate()
    If Not IsNull(Me.cboGroup) Then
        Me.cboAnimalID.Requery
    End If
End Sub

and hope to be OK.

It is working ok. Have a question ? If the type or location are changed for any reason, could I just put a requery in the 'On Change'. Example: A person has 'sheep' in the 'main pasture' in the group 'active'. They also have guard 'dogs'
in the 'main pasture' in the group 'Active'. I have pulled up the sheep that are in the main pasture now I need to do the same for the dogs. However, an 'On Change' requery in the type and location would also work the first time. Is there a way around this.

 

[Mihail]

I've implemented a "template" that should help you to use any number of combos in order to filter your DB.

Sorry, but your DB seems to have wrong setup.
For example, the Animal Type field has TEXT data type. This field should be NUMBER (should store the TypeID, not the TypeName).

Also, DO NOT use spaces (or other special characters) in the names (names of fields, tables, queries etc)

If you like, I can help you to (re)design the DB. But, in order to do this, I need to know the entire DB.

 

[Bladerunner]

I've implemented a "template" that should help you to use any number of combos in order to filter your DB.

Sorry, but your DB seems to have wrong setup.
For example, the Animal Type field has TEXT data type. This field should be NUMBER (should store the TypeID, not the TypeName).

Also, DO NOT use spaces (or other special characters) in the names (names of fields, tables, queries etc)

If you like, I can help you to (re)design the DB. But, in order to do this, I need to know the entire DB.

Mihail, thank you for all your help. I understand about the spaces in the names. I did this without thinking of the consequences. I usually program in VB where this does not matter as much. This is my first attempt at the Access Database.

As far, as the "For example, the Animal Type field has TEXT data type.", I understand this as well, however, I am making this DB up from scratch without any outside help (other the great bunch of people at access-programmers) by adding tbls, frms,rpts,etc. as I need them. You see not only do I run a local business, I own a farm that has several different animals, soooooo I am writing this program (DB) on the fly. The text in the [tbl animal setup] allowed me to see the fields before any forms/reports were or are written. It is an ongoing process sometimes limited by the time I have to actually set down and program it out. (i.e. an animal died and to accommodate this, I at that time wrote the table. 'tblLoss' that encompasses all the types of animal losses a farm can have.) It also gives me something to look at when I am testing new codes (i.e. updating queries, etc.). With VB, you can just simply look at the record and go on. In Access you must stay within the framework of the Access program itself. A little restricting but I like it.

In other words, 'I am living in a building, I am building' and sometimes things just don't get done exactly right.

Thanks for your offer but there is no need since this DB is a long way from being finishes. What I can do is with any new programming, fields, tbls, etc. incorporate the correct formats hence-forth!

Again thank you for your help.

Have a nice day :>)
Bladerunner

 

[Mihail]

Glad to help you.

PS:
I "know" VB5 and 6. Now I started to learn .NET .

 

[Bladerunner]

Glad to help you.

PS:
I "know" VB5 and 6. Now I started to learn .NET .

Yeah, I got VB5. Too old to learn much of anything else. Have fooled with other data bases (Rbase, Dbase, etc. and programming languages (basic A, VB, fortrain, C+, Assembly, etc. since the beginning of time (time of the computer) lol. Some of the computers were even programmable by card , Have forgotten most of it but still try to 'keep my hands in the till" from time to time.

Thanks again.


Have a nice day :>)

Bladerunner