A few beginner questions

[Legacy 3234]

I have zero experience with Access and could use some help with a few things. If anyone has time to help and keep the explanations as simple as possible, I'd really appreciate it. (Access 97)

I've created an entry form with some combo boxes. Four of the combo boxes are synchronized and running on queries, two are not.

I want the users to select items from these combo boxes, but what I want to go into my data table are codes representing their choices.

I've gotten it to do this with one of the unqueried combos by using this

SELECT DISTINCTROW [tblCompanies].[CompanyCode], [tblCompanies].[CompanyName] FROM [tblCompanies];

in the Row Source property for that combo box. However, I can't do that with the combos that are running on queries because they already have "qryClassList" (for example) in the Row Source property. I don't know where to begin to figure out how to get around it. I don't even know how I got that code I'm using in the first place. It just magically appeared!


I have a different problem in one of the other combo boxes. The list that feeds it has entries that begin with leading zeros. For example, "01 - Alabama". If I don't use the code above, the data I get in my output is the index number from the table the list is coming from. If I do use the code above, it won't display states that start with leading zeros in the drop-down list. Is there a way to get around that one?

I also want to prevent the user from accidentally entering a record before they're finished by locking the form somehow and having them press a button to add the new record. But I also want it to continue to display their entries until they reset the form with another button. (They'll be making a lot of entries where only one thing from the previous record will change).

Jane

 

[bat17]

I think that the properties that you need to look at are the 'Bound column' and 'Column width' fields.
What normally happens is that your Query/Sql statement returns 2 columns. The first column is the ID for the data being looked up and the second column is the data to display on the form. By setting the first column width '0' you and the second to 4.5cm you hide the id column and show only the "Friendly" names.

The bound column is the column whose data is stored in the forms record source so this is normally column 1.

So the normal set up is
Bound Column = 1
Column Count = 2
Column widths = 0cm;2.54cm ( adjust to suit your units of measure and width of second column)


HTH

Peter

 

[Legacy 3234]

Peter,

Thank you very much for the tips. It took care of the problems I was having with a couple of the combo boxes (although Access still refuses to deal with my leading zeroes but I can deal with that in Excel).

I'm still having trouble with the combos that are tied to queries outputting what I want.

Example:

cboDivisionList uses qryDivisionList, which is based on tblDivisions.

tblDivisions has four columns: ID, DivisionID, DivisionName, DivisionCode

I had the query set up with just the DivisionID and DivisionName with Name sorted ascending but, based on Peter's advice, I think I must need to include Code since that's what I'll want my bound column to be.

The query is now set up like so:
DivisionID(unsorted), DivisionName (sorted ascending), DivisionCode (not sure if/what it should be sorted as, have tried ascending and unsorted)

I've set the combo box properties like so:
RowSource = qryDivisionList
Column Count = 3
Column Widths = 0";1";0"
Bound Column = 3

Now, when I make a selection in the combo I get the error message:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

After I click OK on the error message I'm allowed to continue with my entry and it does send the code I want to my table. I have no idea what this error message is trying to tell me. How can I get rid of it?

Thanks again,
Jane

 

[bat17]

I cant see what would give you the error message, my only thought is that as you are not actualy doing anything with DivisionID(unsorted), try dropping it and having
DivisionCode(unsorted), DivisionName (sorted ascending)

Column Count = 2
Column Widths = 0";1"
Bound Column = 1

HTH

Peter

 

[Legacy 3234]

From what I understand, I need the division ID because of the combo box that follows (and is dependent on) this one. At any rate, I tried dropping it and I still get the error. I give up. I'm just going to convert what I get out of Access into the codes I need after I export it to Excel. It works fine until I try to change the bound column to one that's not what's actually displayed in the combo-box.

Thanks again for all your help.