Capture 24 ComboBoxes user inputs for Reports?

[BennyWong]

Hello All,
I have a form that consists of 24 Comboboxes in which the user can select from. My question is how do I capture the user's input selections and save it into a table for future in generating future reports. If possible, when the
selection is saved to a table that a listbox then displays the available reports for printing. Thanks for you help in advance.

 

[EarS]

You can use this strategy to take the data on a form to update a table. Also with this method you can write you own error checking code to catch errors before you actually update the table!

Hope this code helps!

Matt
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim SQLQuery As String
Dim db As DAO.Database
Dim tbl As DAO.Recordset

set db = currentdb
SQLQuery = "SELECT * FROM your_table_name"
Set tbl = db.OpenRecordset(SQLQuery, dbOpenDynaset)

tbl.AddNew
tbl!table_field_name = form_feild_name
'continue this method for all 24 fields
tbl.update
tbl.close

 

[BennyWong]

Hello Matt,
Thanks for responding so quickly. I'm not too versed in
VBA, however I'm just beginning to learn about it. From what I see on the first three lines is you are doing the declaration portion. Then you are setting setdb as the current database. Then your are using a Select statement with the * as a wildcard from your table name. Does this
mean that when I create a table the result will copy the
combobox setting into the table row? Also I see there is
a comment "continue ths method for all 24 fields", I don't
understand what you mean or what I should do. Thank you very much for your time.

 

[EarS]

Maybe I can be a little clearer. Let me try anyway. You will first need to create a table with 24 fields in it. For example if you called the table "User_Data" you would say
SQLQuery = "SELECT * FROM User_Data"
the next line simply opens the table stated above.
Set tbl = db.OpenRecordset(SQLQuery, dbOpenDynaset)
Next the tbl.addnew
command simply states you are going to add new data. Now here is where it gets tricky so stay with me. if you named the first two fields in the table you created. First_Name | Last_Name
and the data you want to save from the form is named Fname (this woudl be the name of the text box or list box or whatever on the form). Then to save the data from that field into the table you created you would need to say

tbl!First_Name = Fname
tbl!Last_Name = Lname

and so on and so forth for each text box on you form that you would like to save data into the table from.

I hope this helps and please feel free to ask more questions as they arise.

Matt

 

[BennyWong]

Hello Matt,
I think I understand what you are saying however, when I create the table named user_data and enter the 24 fields and make sure that the names matches so when it saves the
user selection it would populate the table in the reference fields. My question is do I create a command button and enter the following selection?
Dim SQLQuery As String
Dim db As DAO.Database
Dim tbl As DAO.Recordset

set db = currentdb
SQLQuery = "SELECT * FROM your_table_name"
Set tbl = db.OpenRecordset(SQLQuery, dbOpenDynaset)

tbl.AddNew
tbl!table_field_name = form_feild_name
'continue this method for all 24 fields
tbl.update
tbl.close

I think this is what I should be doing? If this is
correct then it will lead me to the final part of
which I will create either a combobox and have
it point to the row source which I guess is what the
user selected earlier. If they pick a selection it
will print the report? Is there a way for the user
to enter a comment that would be an identifier as which
report was captured so the user can select that report?
I guess it's a mouthful but its fun. I'm having fun
learning all this and I really appreciate people willing
to help me on my way. Thanks for spending your time in
helping me.

 

[Guest_imported]

No problem Benny! This is how we all learn these things. The best part about VB and VBA is that there is tons of help out there. Yeah when they have the form completly filled out then you would put them a save button on the form that would execute that code when clicked. The reporting mechanism I'm kinda fuzzy on. The way that we do it is you would have a button that said "REPORTS". When the user clicked this button it would open a form that had many buttons on it each running a different report. If you could describe it a little more what you want to do I might be able to be more help with that aspect. If you don't mind my asking where do you work?

 

[BennyWong]

Hi Matt,
Thanks for getting back to me on this posting. The report set up on is based on my employer preference. The form itself consist of 24 comboboxes and each combobox is based on a field from a query. So for example a user can select
a REGION = Northern California
a City = San Francisco
a Title = Fire Chief
and so on. When the user is done he can either print a label, export the data to Excel, or a list of email addresses to be exported into Outlook. However, the boss wants the combobox selection saved so the report can be
picked from a combobox to print the report. So the sequence
he wants is:

1. user picks what type of report: label, email, or excel.
2. user picks from combobox to build report then output.
3. user save the selected combobox selection which inturn
is displayed for future use in a combobox.
So in a future date a user can check to see in a combobox
if a previous saved report exist and just select and print.

This is totally new to me from what I have read in books and I can seem to find anything close. So I really appreciate all the help I'm getting. By the way I just
changed profession to be a programmer, a novice programmer.
I started this job about 2 months ago. I am working for a
company Meyers Nave in San Leandro, California. So I guess
I'm a two months old novice programmer having fun. My previous job was a technician. The technician job market here is very limited. So I took the plunge and do something different. Well, enough about me.

By the way, is there a way with 24 comboboxes when a user
makes their selection to have only one button to print their report? Currently, I have 24 buttons each combobox.
The boss doesn't like that senario. Well, enough said...
Thanks for your time helping me. By the way, where do you
work if you don't mind my asking.

 

[EarS]

Gosh Benny that's still a little foggy to me. I don't understand what you mean by you have 24 print reports buttons. Are you not saving all 24 combo box's and then running a report on them???
1. I would use check boxes when a user clicks on one programatically the other 2 become false: for instance
chkLabel click()
chklabel = true
chkemail = false
chkexcel = false
that way you assure that 2 cannot be selected, with that comfort you may now just check to see which check box is true and perform you action based on that.
2.is this where you want to check for previous reports? This is where I get confused what will the user be choosing here? I think the best way to eliminate 24 buttons is to save the data to a table and then run the report all behind a button. I don't feel I've been much help today and I'm sorry I guess its the friday jitters.

No I don't mind your asking. I work for the Department of Mines Minerals and Energy, in a place called Big Stone Gap Virginia. I'm a student programmer here and am working my way threw college.

 

[BennyWong]

Hi Matt,
Happy Friday! TGIF! Thanks for responding. I have tried using the code you provided and created the table client_data. I created an additional fields created by, report comments, and report date. I then created a listbox
that showed the three fields. I only got the report date field to show current date by using the now() on the default value for that field. I can't figure out how to have the user fill in their name and report description so it will show up in the listbox. I created the button called record selection and on the on_click event I entered the code you provided. It works fine. Thanks. Now how do I print the report when the user makes a selection from the listbox?
The 24 report button was initially created with a parameter query in which the user select a field and type in a selection and it would print the report. My boss decided that was not acceptable and wanted a combobox instead for the selection replacing the parameter query. The now 24 comboboxes is referenced to the 24 fields from the table in which the user can selection from those combinations to generate their reports. I could not figure out how to as of yet when the user selects from the comboboxes selection how to print the report. The parameter query was simple I just create a button along the side of the field and when they press it, it would print for that report. How do I create one button when the user finish their selection and be able to print. As you can see I'm being put into the fire sort of speak in learning this and I really appreciate your help. I hope this clearifies the reporting process. Thank you very much for your time and efforts. If I don't hear from you have a pleasant weekend.