Hello all, once again,

I'm trying to create a data entry form quite similar to a grocery store cashier system.

The header information = date, time, store name,
The body = list of all the purchased items
Footer = Comments, Payment method

I want to have this data entered and stored in a table ( for stock check queries, so on so forth)
The big problem is storing a list box into a table - which as far as I know is not possible.

Any suggestions? addons to Access that I can use?

Thanks for looking into this.

What are you trying to do with this list box exactly? Usually list boxes are used on forms to give the users predetermined data entry choices.

In that case for example if the customer what buy cookies or something and you had a list box with all the different types of cookies in it.

Could you please elaborate a little more on what you are trying to do with this list box?

Thanks!

Listbox is probably a confusing word to use.
I need some kind of table-format data entry.

The data entry screen should look something like:

Date ()
Customer ()
Customer Name ()

Item list purchased Qty UnitPrice Cost
Cookies - Oreo 1 $3 $3
Cookies - Dad's 1 $5 $5
Soda - Pepsi 2L 1 $3 $3
Pizza - Dellissio 1 $9 $9

Total 4 $20

How do I go about storing this data in a table?
Table Structure:
Date - CustomerId- CustomerName- ItemPurchased- Qty-Price

Let's say for simplicity sake ItemPurchased, Qty, and Price are input, and Customer and CustomerName are predetermined (chosen by a combo box)

Thanks again for looking into this

This post has been edited by Limbo: 6 Oct, 2008 - 05:29 AM

You would need the following tables.

Orders
Customers
Products.

The customers can come in and make orders based off of their customer ID number. You can create a form to display the products you have in your products table how ever you like. List boxes, you could have the products listed in text boxes with a “quantity box”, there are a number of ways you can do this.

Once the order is complete and your customer hits a submit button that data would need to be entered into your orders table.

Your table structure could looks like this.

Customer
ID (Primary Key)
Name
Address
Phone
City
State
ZIP

Products
ID (Primary Key)
Name
Price
Amount In Stock

Orders
Order_ID (Primary Key)
Customer_ID (foreign key)
Product_ID (foreign key)
Quantity
Price


Just a quick “draft” if you need more assistance please let me know.

Thanks jjsaw5. This is really helping my brain think .

This is where I am stuck though - connecting the objects in the form to the columns in the table.
There are multiple products for each order.

OrderID- Autonumber, Textbox(autoinput),
CustomerID- Text, Textbox(input) or Combobox(select)
ProductID - Text, ?? , ??
Quantity- Number, Textbox(input)?
Price - Currency, combobox(Recordsource - from ProductQuery)


An alternative design to this -
I create a "Fix" button that will hold the static data in place- ie OrderID and CustomerId
And I enter 1 item at a time and "Submit" it to the table.
And I somehow use a subform/report to display the submitted data on the same screen.

But there again, I'm kinda stuck @ the coding for the subform, because I'm trying to receive data, store it and display it all at the same time.


On a side note- Is there a way to disable/hide the navigation buttons on the status bar? and is there a way to enable/show it? - got it, Form.NavigationBar = True

This post has been edited by Limbo: 6 Oct, 2008 - 07:08 AM

Access has a wizard that will walk you through the set up of your combox boxes and other form items. Have you tried doing that?



I am pretty sure you can hide the navigation buttons, but i don't recall how to do it, let me play around and i'll get back to you on how to do that.

Just found it- Form.NavigationBar = True or ShowNavigationBar = True


But going back to the matter of main concern.
The wizard combo box is a good idea. Solved 1 problem.
Haven't look at other items because they all sound right but they all sound wrong.

Not sure what item to use for ProductId. I could put in a lot of comboboxes and text boxes.

Let's say Customer A purchased 3 items.
The screen will look like

Date 08/08/2008
OrderID - 001
Customer - AID
Customer Name - A

Item list purchased Qty UnitPrice Cost
Cookies - Oreo 1 $3 $3 -> combo box1, textbox1, combobox(lookup)1, textbox(calculation)1
Cookies - Dad's 1 $5 $5 -> combo box2, textbox2, combobox(lookup)2, textbox(calculation)2
Soda - Pepsi 2L 1 $3 $3 -> combo box3, textbox3, combobox(lookup)3, textbox(calculation)3

Total 4 $20

So the table then should look like
OrderId Custname Product Qty
001 A Cookie-Oreo 1
001 A cookie- dad 1
001 A Soda- Pepsi 1

How do I connect the screen input to the database table?

Let's say customer B buys 30items. How should I deal with that?

This post has been edited by Limbo: 6 Oct, 2008 - 08:08 AM

I think instead of trying to explain this to you over the forum, who you be open to me sending your a "Example" database and that way you can actually see how everything is set up?


If you would like to please PM me and we can talk about it.

Thanks a ton jjsaw for your assistance.

Something else kinda came up while I was fooling around Access.

I have a data entry form and I want to enter it into a temp table, before appending it into the master table.

So what I created was
TempTable, ClearQuery, AppendQuery, MasterTable, DEForm

DEForm_Load-
I unbind all objects from the Temptable and run ClearQuery, which adds 0rows to Temptable.

Then it's regular dataentry. And once I'm done, I hit "Submit" and Append all the data into the Mastertable.

Is there an easier way to do this or at least get rid of the System warnings? ( "Are you sure you want to do this?" "You are adding 0 rows to Test")

Is there a way to use datagrid to input data?
Or is there a way to combine data input on a subform and the mainform into 1 table?
Or is there a way to pass a parameter on the main form into the subform?

What I'm using is a tabular data entry form with a column of redundant data that needs to be carried forward. 30 entries- same data
eg:

Date Item sold Quantity


Date is redundant data for the daily input.
I put it in a temp table and append to the master and clear the temp.

Any suggestions?

And yet another problem I'm faced with-

How to update a table of records from info in a temp, and if the record doesn't exist, create a new record?
I can append to add new records and update to update new records.
But how do I do both in 1 button without getting double entries.

--change of plan.
I'm thinking I'll take the master table, filter it and then work on it and finally append it.

I think I know how to run this filter.

Docmd.OpenQuery "Filter",acNormal, acReadOnly
How and where do I include the filter criteria? would it have to be in the query or could I do that in the form somehow?

This post has been edited by Limbo: 7 Oct, 2008 - 12:31 PM