I have a form with a source set as a table with fields [Number] and [Job]. On the form is 26 Combobox's. I want to have each combobox to have a single record of the underlying table as its controlsource. Currently all the combobox's show the current record, when I increment or move records they all show the same. Anyone have an idea on how to do this or is it just not possible?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Multiple Combos on form, each with controlsource set to single record? 1
- Thread starter thefarg
- Start date
- Thread starter
- #23
Yea, I tried that but its looking for a macro. I could do a macro to run the function but thats too much indirection, for my liking and I prefer to have it in code, where I can see and change it, rather than using macro's.
As to the purpose.......I have written an accounting package to replace the one we currently use. We are a pest control company.
Our current package uses Identifiers (A-Z) as a quick way of indicating what type of job it is. This is Typed in at the point of setting up a job/invoice, and can contain multiple entries, concatenated into a string (ACPZ etc) and stored in the field. On a form in the settings, these Identifiers can be associated with a job type (any string, its a multipurpose accounts package).
So I'm using 3 forms. It is a fairly standard order/invoice type form, with a field for the Identifiers. Double clicking in the identifier field brings up another form for those who don't want to type in the identifiers or dont know what they stand for. Thsi form has 26 checkboxes and 26 textboxes. In each text box is shown what that letter stands for, taken from underlying table. The user clicks the corresponding checkboxes and it produces a string that is put in the Identifier field of the order/invoice form.
The form I was having trouble with is a form that sets what each Identifier currently stands for. This form has 26 comboboxes and 26 labels. each labels caption is a letter A-Z. Setting any of the comboboxes changes what the Identfier stands for based on the list or typed entry.
I ended up just looping through all the controls on the form with a recordset from the main table.
Cheers
As to the purpose.......I have written an accounting package to replace the one we currently use. We are a pest control company.
Our current package uses Identifiers (A-Z) as a quick way of indicating what type of job it is. This is Typed in at the point of setting up a job/invoice, and can contain multiple entries, concatenated into a string (ACPZ etc) and stored in the field. On a form in the settings, these Identifiers can be associated with a job type (any string, its a multipurpose accounts package).
So I'm using 3 forms. It is a fairly standard order/invoice type form, with a field for the Identifiers. Double clicking in the identifier field brings up another form for those who don't want to type in the identifiers or dont know what they stand for. Thsi form has 26 checkboxes and 26 textboxes. In each text box is shown what that letter stands for, taken from underlying table. The user clicks the corresponding checkboxes and it produces a string that is put in the Identifier field of the order/invoice form.
The form I was having trouble with is a form that sets what each Identifier currently stands for. This form has 26 comboboxes and 26 labels. each labels caption is a letter A-Z. Setting any of the comboboxes changes what the Identfier stands for based on the list or typed entry.
I ended up just looping through all the controls on the form with a recordset from the main table.
Cheers
That could be simply done with a continous form. You created the Rube Golberg version.
Also you can put a function name into an event property. It is not looking for a macro.
Since access does not have control arrays, this can be used as a way to simulate that.
Also you can put a function name into an event property. It is not looking for a macro.
Since access does not have control arrays, this can be used as a way to simulate that.
I don't agree with "and can contain multiple entries, concatenated into a string (ACPZ etc) and stored in the field". I would not store multiple values in a single field or even in a single record. This type of solution typically leads to threads in forums with over 20 postings and several veterans completely confused.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #26
Just trying to replicate the features of the existing program. The concatenated string may not be how it is implemented in code, but it's how it gets served up to the end user. Both approaches work. I am still interested in looking at the continuous forms idea put forward by MajP, but I have never used them. The microsft description of "Multiple Records in datasheet view" doesnt sound very appealing. Do they appear as normal forms or as a datasheet? I could have 26 yes/no fields in the orders table I guess......
A datasheet and a continous form are not the same. A datasheet is a grid view a continous form has all the features of a form but records appear one after another. Open or Download the Northwind sample database to look at some of these features.
tblInvoice_Services
serviceID (foreign key to table holding a list of services)
invoiceID (foreign key to a table holding invoices)
ex:
1 1
1 2
1 7
2 3
2 9
The above data shows that for invoice 1 they have services (1,2,7)
2 has (3,9)
If I join that table to the invoice query and the services table in a query you would have all the information for the services and invoice.
Need to also read up on relational database design. That is a non normal design and leads to Duane's 20+ postings. Assuming those fields represent services for an invoice then you need a junction table to create a many to many.
tblInvoice_Services
serviceID (foreign key to table holding a list of services)
invoiceID (foreign key to a table holding invoices)
ex:
1 1
1 2
1 7
2 3
2 9
The above data shows that for invoice 1 they have services (1,2,7)
2 has (3,9)
If I join that table to the invoice query and the services table in a query you would have all the information for the services and invoice.
The native way to do this with out coding would be a form, sub form. The main form would be bound to the invoice table, the sub form is bound to a query based on the invoice_services junction table. The main form is single form and the subform is continous form. You would have a combo to pick a service and add to the invoice_Services table in sense adding a service to an invoice. That is the easy pretty standard way, but not best interface. You would probably want a checklist where you can pick from a list by clicking, instead of selecting records and adding. This way requires some code and there is several ways to do it.
I like to use a listview with checkboxes, but those are non-native access controls and require much more coding. A lot of people would use a multiselect list box. The method is the same.
1) You move to or select an invoice from the main form
2) you read a query that returns the services from the junction table where the invoice id equals the current invoice.
3) check them in the listview or hilite in the multiselect
4) if you select a service you do an insert query to put the service id and invoice ID into the junction table
5) if you deselect you do a delete query where service id = the unchecked service id and invoice = the invoice id currently selected
A similar way is to add an "isSelected" field to the services table, and use that in a continous form. Do basically the same as above.
Now I am going to commit Access blasphemy, only because I seem to be one of the few people who understands how this works. Most hardcore Access developers Poo-poo it and never spend the time to understand. The above can be done very easily in Access 2007 and beyond using a multi value field. It can do basically everything I said with little or no code. So when you asked "surely this is common scenario" this is now a solution. The problem with multivalue fields is that it hides what I just described in a normal structure behind the scenes in system tables. So unless you are like me who really understands it, you may end up getting yourself in boxed in.
But using a multivalue field and no code you could save multiple services inside a single field in an invoice table. And I do not care what anyone says, it is in fact a normal database design (although somewhat hidden). You can without code build forms to select from a list of services. I do not recommend them, because few people understand the capabilities and limitations. If you posted a question about them, you are likely to get an incorrect answer even from seasoned developers. If this needs to be compatible with other database applications do not consider it.
I like to use a listview with checkboxes, but those are non-native access controls and require much more coding. A lot of people would use a multiselect list box. The method is the same.
1) You move to or select an invoice from the main form
2) you read a query that returns the services from the junction table where the invoice id equals the current invoice.
3) check them in the listview or hilite in the multiselect
4) if you select a service you do an insert query to put the service id and invoice ID into the junction table
5) if you deselect you do a delete query where service id = the unchecked service id and invoice = the invoice id currently selected
A similar way is to add an "isSelected" field to the services table, and use that in a continous form. Do basically the same as above.
Now I am going to commit Access blasphemy, only because I seem to be one of the few people who understands how this works. Most hardcore Access developers Poo-poo it and never spend the time to understand. The above can be done very easily in Access 2007 and beyond using a multi value field. It can do basically everything I said with little or no code. So when you asked "surely this is common scenario" this is now a solution. The problem with multivalue fields is that it hides what I just described in a normal structure behind the scenes in system tables. So unless you are like me who really understands it, you may end up getting yourself in boxed in.
But using a multivalue field and no code you could save multiple services inside a single field in an invoice table. And I do not care what anyone says, it is in fact a normal database design (although somewhat hidden). You can without code build forms to select from a list of services. I do not recommend them, because few people understand the capabilities and limitations. If you posted a question about them, you are likely to get an incorrect answer even from seasoned developers. If this needs to be compatible with other database applications do not consider it.
- Thread starter
- #29
Thanks MajP. Several of your points were interesting. How could I implement the listview with checkboxes you were talking about?
I am also interested in multi value fields. I have used the attachment field a number of times, so am familiar with using recordset2 etc. How do I set up a field to be a multivalue field. I realise that I wont be able to upscale it to a different engine but that is not a concern for me.
Thanks
Mike
I am also interested in multi value fields. I have used the attachment field a number of times, so am familiar with using recordset2 etc. How do I set up a field to be a multivalue field. I realise that I wont be able to upscale it to a different engine but that is not a concern for me.
Thanks
Mike
Click the FAQ button above and read the threads on listviews. Google multivalue fields and read up on them. Do some homework first, then come back with specifics. I never post a question on any message board on any subject without at first doing all the reasearch I can. You can get a lot better description, tutorials, videos, then I can post in a single thread.
-
1
- #31
Here is a listview checkbox demo I had.
- Thread starter
- #32
- Status
Similar threads
