I am creating a project for school. In this project I have a database which contains tables for each ingredient category/vendor for a group of restaurants. I have queries which show the data from like categories but different vendors. Is there a way make a checkbox which can allow the user to select the output fields from the query?
Check box to select output fields (1 Viewer)
- Thread starter DampFloor
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 08:57
- Joined
- May 7, 2009
- Messages
- 19,231
create a datasheet form for your query.
create a new form with those checkboxes and add the datasheet as subform.
on the Load event of the form, make a checkbox checked so that all fields are selected.
on the click or after update event on each checkbox, you build your recordsource for the datasheet. if checked add the field, if unchecked don't include it:
example, chkField1
AfterUpdate: =IncField()
'''''''''''''''
Public Function IncField()
Dim strFields
If me.chkField1 Then
strFields=strFields & "FieldName1,"
End If
If Me.chkField2 Then
strFields = strFields & "FieldName2,"
End If
If len(strFields)>0 Then
strFields=Left(strFields,Len(strFields)-2)
Me.DataSheetName.Form.RecordSource=" SELECT " & StrFields & " From yourTable;"
End If
End Function
create a new form with those checkboxes and add the datasheet as subform.
on the Load event of the form, make a checkbox checked so that all fields are selected.
on the click or after update event on each checkbox, you build your recordsource for the datasheet. if checked add the field, if unchecked don't include it:
example, chkField1
AfterUpdate: =IncField()
'''''''''''''''
Public Function IncField()
Dim strFields
If me.chkField1 Then
strFields=strFields & "FieldName1,"
End If
If Me.chkField2 Then
strFields = strFields & "FieldName2,"
End If
If len(strFields)>0 Then
strFields=Left(strFields,Len(strFields)-2)
Me.DataSheetName.Form.RecordSource=" SELECT " & StrFields & " From yourTable;"
End If
End Function
I managed to get the datasheet of my query in the form however I am having difficulties with the code for the check boxes. Do I right click on the check box then go "Build Event" followed by putting the code into the VBA builder? I am also a little confused with how to correctly write the code you gave me. Attached is a picture of the table my query produced.
Attachments
-
Table Structure 2.PNG
@DampFloor,
From looking at your query, I do need to ask; Do you have fields in your table that are specific to a certain vendor and product? Or did you do something neat for your query to return ONLY those for a certain vendor/product for the query itself?
If the former, you would want to avoid that at all costs. Hard coding vendors or products means you will ALWAYS have to change your program to account for changes in vendor/product. Better to have those as fields within you "Products" file.
From looking at your query, I do need to ask; Do you have fields in your table that are specific to a certain vendor and product? Or did you do something neat for your query to return ONLY those for a certain vendor/product for the query itself?
If the former, you would want to avoid that at all costs. Hard coding vendors or products means you will ALWAYS have to change your program to account for changes in vendor/product. Better to have those as fields within you "Products" file.
Open the parent form (Form1) and press Alt_F11. You can now see the code he is using. Which part do you have questions on?
Hi Mark,
The fields are drawn from tables which show all the data for each vendor in a specific category of ingredients. so there is a table for each category/vendor combo and i am creating queries so the client can compare vendors for each category. the data for these tables is drawn from an excel sheet which the client states they want to keep. so this naturally seemed like the best place to draw data from. Did this answer your first question?
I attached a picture of the code which i have written in my form. I get a compile error when i re-enter the VBA screen so i believe there is an error. Do i create the check box before writing this code? or does the code do it for me? Please let me know if there is any additional info you need to help me through this. Below is a copy paste of my code as well.
Private Sub Form_Load()
' enable all checkboxes
With Me
.chkSyscoBakedItems.Goods = True
End With
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.id.ColumnHidden = Not (Me.chkID)
Me.Query1.Form.SyscoBakedItems.Goods.ColumnHidden = Not (Me.chkFirstName)
End Function
The fields are drawn from tables which show all the data for each vendor in a specific category of ingredients. so there is a table for each category/vendor combo and i am creating queries so the client can compare vendors for each category. the data for these tables is drawn from an excel sheet which the client states they want to keep. so this naturally seemed like the best place to draw data from. Did this answer your first question?
I attached a picture of the code which i have written in my form. I get a compile error when i re-enter the VBA screen so i believe there is an error. Do i create the check box before writing this code? or does the code do it for me? Please let me know if there is any additional info you need to help me through this. Below is a copy paste of my code as well.
Private Sub Form_Load()
' enable all checkboxes
With Me
.chkSyscoBakedItems.Goods = True
End With
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.id.ColumnHidden = Not (Me.chkID)
Me.Query1.Form.SyscoBakedItems.Goods.ColumnHidden = Not (Me.chkFirstName)
End Function
Attachments
-
Compile_Error.jpg
Vendor should be entered into a field in a table, not be coded into the table name. Same with category. You will want to brush up on data normalization. Your current layout would require you to add more tables as you get more vendors and more categories. If Vendor is one of the fields in your table you can add new vendors easily through a form. Same with Categories. As a mental exercise, think of how much work you would need to do if your "customer" decided to use 60 more vendors to provide 15 categories worth of goods because they move into a new style of cooking.
You would normally not use "With Me" as Me. is easy to include. So rather than taking the time to set up a With/End With just for one check box (or even a dozen) you would be better off doing Me.CheckBox so the editor can catch mis-typed control names. To do this, you would need to first add the check boxes as controls on your form though.
I do plan on adding Vendor and Category to each table to assist in vendor quote selection in the future. However, the client only uses 3 vendors and has only for years. Also this client runs many styles of a restaurants so their category list basicly encompasses all foods. They have an excel file (which is linked to the database) which they said they will always use. This excel file is broken into different sheets for every category. My aim right now is to get the check boxes working. Though i still cannot get those going. I am very new to this so your recommendation of .Me flew right over my head. below is what i currently have written and attached is a picture of my form. I starting with getting the check box for "Brand" working as a starting point.
Private Sub Form_Load()
' enable all checkboxes
With Me
.chkSyscoBakedGoods.Brand = True
End With
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.SyscoBakedGoods.Brand.ColumnHidden = Not (Me.chkSyscoBakedGoods.Brand)
End Function
Private Sub Form_Load()
' enable all checkboxes
With Me
.chkSyscoBakedGoods.Brand = True
End With
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.SyscoBakedGoods.Brand.ColumnHidden = Not (Me.chkSyscoBakedGoods.Brand)
End Function
Attachments
-
Form_ScreenShot.jpg
Alright, first part.
Instead of
Most would simply use
"Me." tells Access you are referencing a control on within the current object.
As to having Category and Vendor fields inside one table rather than encoding this data into table names, this will make some coding very simple while your existing structure will make it very difficult. The argument "We've only ever done X BEFORE" is something most developers hear. We learn quickly that it means "We used to do it this way, but once you get your system done we'll start finding things we didn't tell you about". Best practice in data normalization is there because it allows users to use your system when their circumstances change even if THEY didn't think about it before.
As you are seeing, having a TABLE dedicated to Sysco products that are categorized as BakedGoods could as easily be done with one table that has Vendor where you put the value of "Sysco" and category with a value of "BakedGoods".
As you started this thread with "I am creating a project for school.", I'd highly suggest having a discussion with your instructor regarding data normalization. Were I the instructor you would definitely be given a worse grade for ignoring a fundamental concept in database design.
Instead of
Code:
With Me
.chkSyscoBakedGoods.Brand = True
End With
Code:
Me.chkSyscoBakedGoods.Brand = TrueAs to having Category and Vendor fields inside one table rather than encoding this data into table names, this will make some coding very simple while your existing structure will make it very difficult. The argument "We've only ever done X BEFORE" is something most developers hear. We learn quickly that it means "We used to do it this way, but once you get your system done we'll start finding things we didn't tell you about". Best practice in data normalization is there because it allows users to use your system when their circumstances change even if THEY didn't think about it before.
As you are seeing, having a TABLE dedicated to Sysco products that are categorized as BakedGoods could as easily be done with one table that has Vendor where you put the value of "Sysco" and category with a value of "BakedGoods".
As you started this thread with "I am creating a project for school.", I'd highly suggest having a discussion with your instructor regarding data normalization. Were I the instructor you would definitely be given a worse grade for ignoring a fundamental concept in database design.
I have changed my code to what you suggested and I get a compile error (Method or data member not found) when i try to open the form.
I am currently working on getting all the data from the Excel sheet into one table with vendors and categories as fields. But I still need to get the check box's going in the compare queries.
I am completing a business program and this is a final project where i was placed with a random client. So in short, my instructors have zero knowledge of access or programming in general. Everything I am attempting is self taught, so there is likely a few things i do in an "odd" fashion.
I am currently working on getting all the data from the Excel sheet into one table with vendors and categories as fields. But I still need to get the check box's going in the compare queries.
I am completing a business program and this is a final project where i was placed with a random client. So in short, my instructors have zero knowledge of access or programming in general. Everything I am attempting is self taught, so there is likely a few things i do in an "odd" fashion.
Can you post the code that is giving the error?
I'm surprised your instructor would allow you to do a project that they, themselves, would not be able to assist with. For your business program, what is the actual assignment?
I'm surprised your instructor would allow you to do a project that they, themselves, would not be able to assist with. For your business program, what is the actual assignment?
Private Sub Form_Load()
' enable all checkboxes
With Me
Me.chkSyscoBakedGoods.Brand = True
End With
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.SyscoBakedGoods.Brand.ColumnHidden = Not (Me.chkSyscoBakedGoods.Brand)
End Function
It is odd, I agree. I have never even taken any sort of programming courses. It is essentially a year long final project for the program. Though my instructors are very aware of my lack of experience so have been pleased with the progress thus far.
Several real businesses submit projects to the school for their students to accomplish. My groups project is to improve efficiencies in data entry for a company which runs a group of restaurants. Originally I thought our goal was going to be to research third party software for the client and present a business case for it. However, the client came to us with a zero dollar budget so in terms of free software, we believed this was the best option. I should also add that Access is a skill I believe will help me in my future endeavors.
' enable all checkboxes
With Me
Me.chkSyscoBakedGoods.Brand = True
End With
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.SyscoBakedGoods.Brand.ColumnHidden = Not (Me.chkSyscoBakedGoods.Brand)
End Function
It is odd, I agree. I have never even taken any sort of programming courses. It is essentially a year long final project for the program. Though my instructors are very aware of my lack of experience so have been pleased with the progress thus far.
Several real businesses submit projects to the school for their students to accomplish. My groups project is to improve efficiencies in data entry for a company which runs a group of restaurants. Originally I thought our goal was going to be to research third party software for the client and present a business case for it. However, the client came to us with a zero dollar budget so in terms of free software, we believed this was the best option. I should also add that Access is a skill I believe will help me in my future endeavors.
Code:
With [COLOR="Red"]Me[/COLOR]
[COLOR="red"]Me.[/COLOR]chkSyscoBakedGoods.Brand = True
End WithShould be simply
Code:
Me.ChkSyscoBakedGoods.Brand = TrueThere should be no WITH / END WITH around it.
As to the zero dollar budget, my condolences.
How are they currently doing data entry? Where does the data come from? And are you going outside the scope with your reporting? One of the most difficult problems a developer ever encounters is not doing something that benefits the client if the client isn't paying for it (specifying in your case). And if the core is data entry, then you would need to justify how importing from spread sheets to an access database makes data entry more efficient.
I am still getting the same error. When Open the form it goes to the VBA screen and has the "chkSyscoBakedGoods.Brand" portion highlighted. Below is all the code.
Private Sub Form_Load()
' enable all checkboxes
Me.chkSyscoBakedGoods.Brand = True
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.SyscoBakedGoods.Brand.ColumnHidden = Not (Me.chkSyscoBakedGoods.Brand)
End Function
Do I need to create my checkbox in a specific way? I feel like the one I created as no relation to the code I have written besides the textbox beside it which has the same name as the field I am trying to hide.
Currently the client receives quotes from vendors and puts them into the excel file which is linked to this database. Originally the plan was to have the database linked to the quotes from suppliers which would eliminate the need for the secondary excel file. The client however, does not want to give us access to the sheets from suppliers. This was realized far into the project. That being said, the data then flows into their engineered menus which takes the cost of ingredients and produces the cost for the actual menu items. The hope is that this database will perform this automatically.
Private Sub Form_Load()
' enable all checkboxes
Me.chkSyscoBakedGoods.Brand = True
End Sub
Public Function chkStatus()
' we just Hide/Unhide the column depending
' if the corresponding checkbox is checked or not
Me.Query1.Form.SyscoBakedGoods.Brand.ColumnHidden = Not (Me.chkSyscoBakedGoods.Brand)
End Function
Do I need to create my checkbox in a specific way? I feel like the one I created as no relation to the code I have written besides the textbox beside it which has the same name as the field I am trying to hide.
Currently the client receives quotes from vendors and puts them into the excel file which is linked to this database. Originally the plan was to have the database linked to the quotes from suppliers which would eliminate the need for the secondary excel file. The client however, does not want to give us access to the sheets from suppliers. This was realized far into the project. That being said, the data then flows into their engineered menus which takes the cost of ingredients and produces the cost for the actual menu items. The hope is that this database will perform this automatically.
- Local time
- Yesterday, 20:57
- Joined
- Feb 19, 2002
- Messages
- 43,258
Why would you box yourself in this way? You are forcing yourself to create THREE of everything!!!! And then should the situation change, you have created a maintenance NIGHTMARE to add a fourth vendor. A relational database is very different from a spreadsheet where this would be common practice. I hope you are getting paid by the hour because you are grossly increasing the development cost and lifetime maintenance cost.
Please stop and rethink the schema before continuing. What you are doing is wrong and there is simply no gentle way to put it. I'm pretty sure I responded to some of your earlier questions with the same advice.
This current problem is a direct result of an improper table design.
DampFloor,
For your class, how long until you need to provide a deliverable? Can you talk to your instructor (as this is a class project) and let them know you've learned that your initial approach would not work? Is this the kind of class where a paper outlining how your initial try does not meet the requirements would be as worthwhile as a perfect solution?
For your class, how long until you need to provide a deliverable? Can you talk to your instructor (as this is a class project) and let them know you've learned that your initial approach would not work? Is this the kind of class where a paper outlining how your initial try does not meet the requirements would be as worthwhile as a perfect solution?
