Anyone here good with MS Access 97?


(IMProvisar) #1

Ok, I’m working on this inventory/POS project for school. Right now, I have a form to enter a sale, with all customer info, etc. There is a subform for the list of products in the sale, on each line of the subform, you enter the product ID, and quantity, it displays the extended price (retail x quantity) on each line, then in the subform’s footer calculates the sum of the extended prices. The main form reads that control, which is not visible, subtracts any discounts added, and displays the subtotal on the main form (No taxes, since I won’t be hooking it up to a register, the process is you enter in the subtotal, and cashout that… customer recieves a copy of the sales form with the list of products/prices).

There is a SALES table which holds information on date/time of transaction, employee entering transaction, and customer making the transaction. Then a SALES-PRODUCT table with the sale-ID, Product-ID, and Units-Sold. Here’s a pic…

http://mysite.iptic.com/improvisar/misc/2002_12_20_salesform.gif

Problem I have is I need some way to update the product’s stocklevels when the sale is completed. Currently I have a “stocklevel” field in the PRODUCTS table, and I want to be able to just read the units sold from the form, and adjust the stocklevel on each product accordingly. I could just put a button on it to take care of that, but if someone didn’t push the button, the sale would be there, but the stock not adjusted. Also, if someone decides not to buy half way through, you’ve got to delete each field in the subform, then the sale record itself. Is there any way to keep it from updating ANYTHING, even creating the records, until you hit something like a “Complete Sale” button?

Any other thoughts would be greatly appreciated (as well as decent places to get info on the net… I had a hard time finding stuff).

One other question I have… I created a form to make it easy to search through the customers, and find the right one (if they don’t have their membership card… it’s a homebrew store, the memberships are for newsletters, taking brewing courses, and workshop access). I didn’t want the fields to be edited, so I tried just disabling them, unfortunately, they tend to turn gray anytime they’re re-drawn when doing things like sorting or applying filters. Any way to keep it from doing that? I do it now by simply locking them, and keeping them enabled, but then it functions like a text box… I’d prefer for it to work more like a label (too bad you can’t bound labels… or can you?).

Thanks in advance if there are any Access gurus here, :smiley:
Imp

Oh, and if anyone is wondering, all the name/address stuff is stored in multiple fields (fname, mname, lname, etc.), they just display together in calculated controls.


(Cativo) #2

Not much help: you need to use sql, create a formula that subracts the product sold from the inventory. Another way is using Visual Basic, but that’s programming. Sorry I can’t give you any more help, I hardly remember sql. :frowning:


(IMProvisar) #3

Hehe, thanks anyhow. I think I could get it done where a button would update the inventory… perhaps build it into button I plan to make to create a report of the transaction for clean printing… that would help insure against ID10T errors, but it’s still not foolproof enough for me to be confortable with it. :frowning:

That’s the one and only thing that has really bugged me about my degree program… most of the focus (other than liberal arts and math) has been C++ programming. There only one DB requirement, and that’d “Database Theory”… then comes final project, and do we write a C++ program? No… a database, lol. If I’d’ve know, I would have gone out of my way to try and get an Access course (not even sure if they offer one, lol). Id’ve had this program done months ago if it had been something like writing some public-key encryption software (did that in algorithms and had soooo much fun… by the end I knew more about RSA encryption than my instructor… hehe, when I turned it in, and he was done looking at it, he said I had gained his respect as a geek, lol.)

Imp <------------------------------------------------------------------------------------------------Someone who hates databases.


(Cativo) #4

I for one like databases, Access specially. SQL Im not fond of…
what’s your major? CompScience? You’re doing advance stuff with c++.


(IMProvisar) #5

Not really advanced… just had some fun in algorithms. It was only 13 pages of printed code, I think… toughest part about it were long integers weren’t large enough, hehe… had to create a larger integer class (luckily it didn’t need all the same operands, hehe). :slight_smile:

Imp


(IMProvisar) #6

Oh… and I didn’t get into generating large primes… just picked randomly from a list of primes less than about 250.

Imp


(theeth) #7

ok, I’ve work in Access 97 all summer and part time since september, so I know a little tricks and stuff.

I don’t know how good your skills are in VBA (if any), but what you could do is bind the update functions to the From_Unload event, that way, the sales would only update themselves when the user closes the form, which would indicate that all the information entered is correct.

I hope that helps.

Martin


(RipSting) #8

I’ve created an access 97/2000 POS database with my dad and we’ve sold it to a few companies, including the Oregon State Legislative Supply. And yes, we do have a “complete sale” button which does a db.execute to run an update on the inventory table and the history table.

One thing that is very usefull on a scren like this are lookup buttons. Right now you have to manually enter the Customer ID and each Pruduct ID. Why not make those combo boxes with a list of all the posible values? You could merge the customerID textbox and the Name lable into one two-column combo box. The datasource behind the combo box would be tied to the customer ID, but you man make the non-bound column (the name) be the text that is displayed.

For the products, you could also include a search button which would place filters on it (or even change the combo box’s control source). For example they enter “Pen” in the input box when they click the search button. It would then re-populate the text box with only items that have “pen” in their description.

Private Sub cmdFind_Click()
Dim txtSearch as String
txtSearch = InputBox("Enter part of the name or press [Enter]")

'Using If len(txtSearch) = 0 is faster than If txtSearch = ""
If len(txtSearch) = 0 then
...'Nothing entered, so reset the combo box's record source
...cboItem.RecordSource = "SELECT ProductID, ItemName FROM tblInventory ORDER BY ItemName"
Else
...'Change the combo box's record source to only display results with
...'the search string in the name
...cboItem.RecordSource = "SELECT ProductID, ItemName FROM tblInventory " & _
......"WHERE ItemName LIKE '*" & txtSearch & "*'  ORDER BY ItemName"
End If

cboItem.Requery
cboItem.SetFocus
SendKeys "%{down}" 'Drops down the list

End Sub

Sorry if I made a typo, I typed it from memory.


(IMProvisar) #9

Cool, thanks guys. I am working on some forms for searching for customers, and searching for products, but I don’t think they’d be used often (if this were for real), because the business model has members with membership cards (so the clerk can just look at the card for the Customer ID, they only need to search for the right one if the customer lost their card), also, the price stickers on the products would have the product ID on them, and in a theoretical later version, bar codes.

My biggest problem: POS is boring, lol.

Thanks,
Imp