Access Form -Data Entry

Grocery Bill Style

Page 1 of 1

12 Replies - 5282 Views - Last Post: 09 October 2008 - 02:19 PM

#1 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Access Form -Data Entry

Posted 02 October 2008 - 01:27 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Access Form -Data Entry

#2 jjsaw5   User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 3,063
  • Joined: 04-January 08

Re: Access Form -Data Entry

Posted 04 October 2008 - 03:26 PM

View PostLimbo, on 2 Oct, 2008 - 03:27 PM, said:

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!
Was This Post Helpful? 0
  • +
  • -

#3 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 06 October 2008 - 06:28 AM

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: 06 October 2008 - 06:29 AM

Was This Post Helpful? 0
  • +
  • -

#4 jjsaw5   User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 3,063
  • Joined: 04-January 08

Re: Access Form -Data Entry

Posted 06 October 2008 - 06:55 AM

View PostLimbo, on 6 Oct, 2008 - 08:28 AM, said:

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



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.
Was This Post Helpful? 0
  • +
  • -

#5 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 06 October 2008 - 08:04 AM

Quote

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

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


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: 06 October 2008 - 08:08 AM

Was This Post Helpful? 0
  • +
  • -

#6 jjsaw5   User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 3,063
  • Joined: 04-January 08

Re: Access Form -Data Entry

Posted 06 October 2008 - 08:10 AM

View PostLimbo, on 6 Oct, 2008 - 10:04 AM, said:

Quote

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

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


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



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.
Was This Post Helpful? 0
  • +
  • -

#7 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 06 October 2008 - 09:02 AM

Quote

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: 06 October 2008 - 09:08 AM

Was This Post Helpful? 0
  • +
  • -

#8 jjsaw5   User is offline

  • D.I.C Lover
  • member icon

Reputation: 92
  • View blog
  • Posts: 3,063
  • Joined: 04-January 08

Re: Access Form -Data Entry

Posted 06 October 2008 - 09:40 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.
Was This Post Helpful? 0
  • +
  • -

#9 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 06 October 2008 - 10:40 AM

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")
Was This Post Helpful? 0
  • +
  • -

#10 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 07 October 2008 - 09:29 AM

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?
Was This Post Helpful? 0
  • +
  • -

#11 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 07 October 2008 - 10:44 AM

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?
Was This Post Helpful? 0
  • +
  • -

#12 Limbo   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 138
  • Joined: 08-March 06

Re: Access Form -Data Entry

Posted 07 October 2008 - 12:15 PM

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: 07 October 2008 - 01:31 PM

Was This Post Helpful? 0
  • +
  • -

#13 dlkirk03   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 36
  • Joined: 12-September 08

Re: Access Form -Data Entry

Posted 09 October 2008 - 02:19 PM

You can do it in a form through the VB code but you must have something to trigger the filter. For instance if you had a combo box you could use the onchange then put the expression in the VB code to filter the showing results.

Your earlier post referring to moving information from a Temp table to a master is possible if you go to the vb code and have it execute a query that pulls all the records looks at the related key records in a loop if it does not exist run an append query with the record data, if it does exist run an update query. Then go to the next record. I like to do this by using a do while RS.EOF = false (RS = the selected record set). You can use RS!fieldname (where field name is the name of the field) to use the selected field name to input the text or check if it exists.

Also if you want to delete all the records in the database just run a delete query in the VB code and it will not show any questions.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1