control-srcing a query

[bpaquette]

so i have a combo box with a list of names sourced from my personnel roster. upon selecting a name, it references a query that holds the fields name, trained, and grade. it then populates a trained and grade txt box

the criteria on name is Forms.frmPersonnel.cboRatersname

i get the feeling this won't work properly. Mainly because it doesn't work

am i doing something wrong? am i being too vague in my description?

 

[Mile-O]

Wouldn't it be easier to make a query that pulls the information you want and have that act as the RecordSource for your combobox?

Then you can set the column width properties of those extra fields to 0 and can then set the ControlSource of the textboxes to reference the hidden columns in the combobox.

 

[bpaquette]

really that's what i'm doing. qryRaterInfo is the source for all of the txt boxes. When i try to set their control src to a field from the query, it just gives me the #Name? value in the txt boxes. running the query works fine but using it as a control source for txt boxes doesn't work. the problem with using it as a row source is that i need it's control source to be a field in the record of the rater, NOT the record currently loaded into the form.


so if my table reads:


brandon MAJ yes no
John Col no no


if Brandon is the record loaded in the form, this text box has to read the information for John.


is this more clear? im finding it difficult to explain for some reason.

 

[Mile-O]

Set the ControlSource not to the fields in the query but to the columns of the combobox.

i.e.

=[cboMyCombo].[Column].(1)

 

[bpaquette]

oh so the row source of the ratername combo box should have the columns i want to reference? ie grade and ratertraining? and then i can reference them via the syntax you listed?


i didn't even know that was possible

thanks miles i'll post how it turns out!

 

[bpaquette]

er i dont nkow that this is working.


the row source of cboRaterName lists all the necessary columns with only the name showing. i then reference the other columns as txtRaterTraining's ctrl src via

=[cboRatersName].[Column].[Army]


with all that done i still ge the #Name? value

did i screw it up?

 

[Mile-O]

oh so the row source of the ratername combo box should have the columns i want to reference?

No. The rowsource of your combobox is the query you have already defined. Once the query is defined as the RowSource you change the Column Count to the number of columns in your query. Now, set the Column Widths for each column (for example, if you had 5 columns but only wanted to show the first column then you could enter: 2;0;0;0;0

The ControlSource of the textboxes would reference the specific columns that contain the data you wish to show. (Bear in mind that Access columns start at 0 so to reference the first column would be: =[cboMyCombo].[Column].(0)

 

[bpaquette]

k i'm getting syntax errors with =[cboRatersName].[Column].(0)

where in the helps can i find reference to this/ a search yielded nothing...

 

[Mile-O]

Can you post a sample in Access '97 of what you are trying to do?

 

[bpaquette]

i can try gimme 5 min.

 

[Mile-O]

Here's an example I've done before of referencing a column in a combobox:

 

[bpaquette]

here you go. made in access 2002 and converted.

 

[bpaquette]

i just saw the syntax error after looking at you working code. there is no . between column and (0)


let me try this!

 

[bpaquette]

This works great, and in addition to solving this problem this will let me fix quite a few things with my db! thanks mile!

 

[Mile-O]

A few things you might want to consider based on what I've just seen in your example:

• There is no primary key in your table;
• Don't name a form with the tbl prefix; use frm instead;
• It is a better preactice to split names into different fields: i.e. Forename, Surname, etc;
• Your table isn't good as it contains a heap of information that, although it may be related to each other, is certainly not dependant upon any other field in the table. You should split these down into individual tables;
• Having a Rater also being a name is a repeating group and can be condensed to one field;
• Don't use reserved words for naming objects in Access: Name, in this case, is a reserved word;
• Fields where you have two options (Yes and No) are better off as Boolean Fields (Yes/No) rather than text explicitly stating Yes and No;
• Keep to a standard. In some places you have used all lower case, other areas, propercase.

Good luck...

 

[bpaquette]

lol you must think me a horrible designer based on that! that wassomething i scrapped together in a few minutes, my table is nothing like that


except for all my data stored in the same table. i've still failed to see a need in my development for a relational database (although i do maintain primary keys in the few tables i do have). But I'm keeping it structured properly in case i do see the need.

Thank you for your feedback though

 

[Mile-O]

i've still failed to see a need in my development for a relational database (although i do maintain primary keys in the few tables i do have)

From what I could see: a table for Personnel and a table for Ranks.

 

[bpaquette]

wow. for some reason it just clicked that i could use several relationships. ack, this is gonna involve some redoing


thanks again M

 

[Mile-O]

I'll refer you to this thread - Hayley posted a good tutorial on there and I've listed the Microsoft Tutorial.