customizing a report based on data in current form 2

[miscluce]

I am trying to link an object on a report to a lstbox on a form. when you click a button the report supposed to open up with the same data that was in the form but the data from the list box is not showing up.

here is my control source for the object on the report:
control source =[Forms]![frmDoughnuts]![lstItems]

can anybody tell me if I linked this object right because it is blank on my report?

 

[lameid]

I think your problem is the square brackets around forms...

=Forms![frmDoughnuts]![lstItems]

In this case, there are no spaces or special characters in the form or control name so you do not need any square brackets at all but as above they are not incorrect either.

 

[Remou]

Is list items a listbox? If so, are you sure that something is selected and that it is not a multiselect listbox?

 

[miscluce]

I used an expression builder to write this "=Forms![frmDoughnuts]![lstItems]"


Yes, lstitems is a list box. Well, I am not selecting anything I am just populating it with items from a click event with a combo box. and I want to print that in the report. I also would like to run a loop to add those items in the list box which is (ex. Coffee $2.00) but I haven't figured out how to assign a value(ex. $2.00) to each item in the list box yet.

And then print it out like a reciept.

 

[Remou]

If you have not selected anything, then the listbox does not have a value, so it won't show in your report.

Are you sure that you want to do this this way? The usual way would be to have a subform based on, say, an order items table. Once you do that life becomes much easier.

Have you had a chance to look at the Northwind sample database?

 

[miscluce]

WOW, that sample database(Northwind) is cool but that is to advanced for me to do. I could do something like that if I had a book or something that should me those steps. I wonder if Microsoft has a tutorial on how to create that database. Although, I know how to do some of that functionality in the project.

No, I dont have to do it with a list box. I was not aware of the capability access has. I am an old school programmer.

I have used a subform but calculating cells in the datasheet I need to figure out how to do. I have only been messing with programming applications with access for two weeks now. Access doesn't seem hard once i get familiar with all the tools.

 

[miscluce]

about the order table. i am not sure what you mean. Do you want me to do an sql insert query everytime you click on an item and then have the subform bound to that table??

 

[lameid]

Typically you would have a subform bound to the table to have the items related to the main form. You can specify Master and child link fields (similar to a join).

Then you simply enter things into the subform. The usual solution to this situtation is to use a combobox on the subform to select values instead of a listbox. This is the approach mostly because it is easier.

Although you could select from the listbox and append/insert into the target table. If you did this, you could do it on the click event of a button or the double click event of the listbox. If you go down this route you should check out docmd.setwarnings and the Requery method.

 

[Remou]

Yes. Running a query would be a good idea, and easy to do. Then everying would fall into place. You could use the new table to print receipts and so on. It would work something like this:

strSQL="Insert Into tblOrderItems (OrderID, ItemID, Quantity, Price) Values (" & Me.OrderID & "," _
& Me.cboItems & "," & Me.txtQuantity _
& "," & Me.cboItems.Column(1) & ")"
CurrentDB.Execute strSQL, dbFailOnError
Me.[OrderItems Subform].Form.Requery

 

[Remou]

Lameid's suggestion is probably the better option.

 

[miscluce]

Lameid's suggestion.

thats what I was going to figure out. I was just going to replace the listbox with a subform and populate that with the items from a combo box selection and then add them up and print out that in a report.

FYI- I only have one form with two combo boxes and one subform and 3 textboxes at the bottom for tax, subtotal, and total. but I am guessing I can get rid of those textboxes if I am using a subform for everything. ??

 

[lameid]

Each form in this case is a table. Which fields go where is a matter of database normalization. I would think you would definitely have things like quantity and maybe price in the detail. When this detail is associated with an Invoice, I would expect the invoice header record would have the total invoice amount and any other relevent totals. This is more intentional denormalization on the Invoice header for reporting.

 

[miscluce]

thanks for the replies. I am in the process on working on this.

 

[miscluce]

I have three tables(tblorders, tblbevarges, tbldoughnuts) no relations(keeping it simple)

I have the subform source object bound to my tblorders table. When the user makes a selection in one of the cbobox
it fires the below event which is supposed to add items in the subform.

But it says member not found for cboitems. The intellisense is not showing the controls in the list.

can someone tell me if this is correct:

Private Sub cboDoughnuts_Click()
strSQL = "Insert Into tblOrders (items, Price, quantity) Values (" & Me.cboItems.Column(1) _
& Me.cboItems.Column(2) & "," & Me.txtQdg & ")"
CurrentDb.Execute strSQL, dbFailOnError
Me.[tblOrders].Form.Requery
End Sub

in tables:
column 1 = item
column 2 = price
me.txtqdg = "whatever number user inputs in text control for quantity"

 

[Remou]

You are missing a comma.

[tt]strSQL = "Insert Into tblOrders (items, Price, quantity) Values (" & Me.cboItems.Column(1) & "," & _
& Me.cboItems.Column(2) & "," & Me.txtQdg & ")"[/tt]

Be aware that you will need single quotes for text fields.

 

[miscluce]

NOw i have too few parameters expected 1. I checked all my feilds they look fine to me. Do you see anything?

tblorders
fields(items, price, quantity)

Code:
Private Sub cboBeverages_Click()
Dim strSQL As String
strSQL = "Insert Into tblOrders (items, price, quantity) Values(" & Me.cboBeverages.Column(1) & "," & Me.cboBeverages.Column(2) & "," & Me.txtQdg & ")"
CurrentDb.Execute strSQL, dbFailOnError
Me.tblOrders.Form.Requery
End Sub

 

[Remou]

What types are the fields, that is which are text and which are number fields?

I would use the After Update event, not the Click event.

 

[miscluce]

integer= number
price= currency
items= text

 

[lameid]

strSQL = "Insert Into tblOrders (items, price, quantity) Values('" & Me.cboBeverages.Column(1) & "' ," & Me.cboBeverages.Column(2) & "," & Me.txtQdg & ")"

OR

strSQL = "Insert Into tblOrders (items, price, quantity) Values(""" & Me.cboBeverages.Column(1) & """ ," & Me.cboBeverages.Column(2) & "," & Me.txtQdg & ")"

I prefer the latter, Remou surely the former based on his earlier post.

 

[lameid]

Oops, missed the wrapping text and Remou is right about not using the Click event as the data in the combo is probably not avarailable when you click it.