The easiest way to make a point of sale

[jzwp11]

First, the syntax of your DLookup() functions is incorrect. Second, you have to tell the Dlookup() the itemID that was entered in the textbox control (i.e. the criteria). Third, I believe you have to enclose the table name in square brackets because of the hyphen in the name.

DLookup("fieldtolookup","queryortablename","criter ia")

fldItemDesc = DLookup("ItemDesc", "[ItemInfo-T]", "ItemID=" & me.itemtextboxname)
fldItemCost = DLookup(ItemCost, "[ItemInfo-T]", "ItemID=" & me.itemtextboxname)




I'm not sure why you would repeat the itemID in fldItemID since your user just typed it in the item textbox. The me.itemtextboxname represents the name of the control on your form where the ID is entered.

The ItemID shown in red represents the name of the itemID field in the iteminfo-T table (you will have to substitute the actual field name)

Now, what is the datatype of the itemID field in itemInfo-T?

[eskybel]

Thank you for the clarification. I was playing with the brackets and quotes trying to see which should be used, and looking at it, I believe you're right on the brackets. So, I changed that portion. Then, I didn't have any criteria, because I wasn't sure how to make it point to what was typed, which is why I tried using the variables, but obviously, I got lost in all of that again.

Ok, I've also added the text in red, corresponding to each field in the table, and the blue corresponding to the textbox names. I ended up using the same names for the fields in the table, and the names of the text boxes...is that a problem?

So, here's what it looks like now:

Dim fldItemID, fldItemDesc, fldItemCost As String

fldItemID = DLookup(ItemID, [ItemInfo-T], "ItemID=" & Me.ItemID) <-----just delete this one because it was just typed?
fldItemDesc = DLookup(ItemDesc, [ItemInfo-T], "ItemDesc=" & Me.ItemDesc)
fldItemCost = DLookup(ItemCost, [ItemInfo-T], "ItemCost=" & Me.ItemCost)

[jzwp11]

Every term in the DLookup() must be enclosed within quotes except the variable (i.e. the itemID) which is concatenated to the criteria section with the &. The criteria must be the ItemID in all 3 Dlookup() function since that is what you are trying to find in order to match to what was input by the user

fldItemID = DLookup("ItemID", "[ItemInfo-T]", "ItemID=" & Me.ItemID)
fldItemDesc = DLookup("ItemDesc", "[ItemInfo-T]", "ItemID=" & Me.ItemDesc)
fldItemCost = DLookup("ItemCost", "[ItemInfo-T]", "ItemID=" & Me.ItemCost)

[eskybel]

Ok, thanks so much for the help again!

The datatype is number for ItemID.

Here's what it looks like, minus the fldItemID variable:

Dim fldItemDesc, fldItemCost As String

fldItemDesc = DLookup("ItemDesc", "[ItemInfo-T]", "ItemID=" & Me.ItemDesc)
fldItemCost = DLookup("ItemCost", "[ItemInfo-T]", "ItemID=" & Me.ItemCost)

When I enter a value in the ItemID field, I get a "syntax error (missing operator) in query expression 'ItemID='.

[jzwp11]

My apologies I forgot an edit

fldItemDesc = DLookup("ItemDesc", "[ItemInfo-T]", "ItemID=" & Me.ItemID)
fldItemCost = DLookup("ItemCost", "[ItemInfo-T]", "ItemID=" & Me.ItemID)

[eskybel]

Disregard the first version of this post if you saw it, I made a mistake...I didn't have the ID I was entering in my ItemInfo-T, so it wasn't finding it.

Ok, so fixing the red fixed it. Thanks! So I used a couple other itemIDs, and it proceeded to the next field, ItemQty, so no errors at the After Update. Now, to populate the other text boxes.

I also just realized, I Dim'ed each of the variables as strings...should they be:
ItemDesc as string, ItemCost as currency?

[jzwp11]

If you want to see the value returned by the Dlookup() on your form, you need to assign that value to a control on the form. So instead of assigning the returned value to a variable, just assign it to the control

me.somecontrolname = DLookup("ItemDesc", "[ItemInfo-T]", "ItemID=" & Me.ItemID)

[eskybel]

Ok, where would I put that line of code? Instead of using the variables in the lines above, just use that line?
EDIT: Yep, just tried it, and it did fill the info in. Now, another wrench...After Update, the information populates, and I am at the field to enter the quantity of the item purchased. I guess this is the point where some math has to happen to alter the base cost of the item to the mutliple cost.

And, since the ItemQty, ItemDesc, and ItemCost are all fields in the table the subform is displaying, will that data be updated in the table itself?
Edit: Yes, it appears after I close the form (and save) it does update the table. So, a lot more functionality than I started with today!

[jzwp11]

And, since the ItemQty, ItemDesc, and ItemCost are all fields in the table the subform is displaying, will that data be updated in the table itself?

If the controls are bound to the fields in the underlying table, yes, the table should be updated. BTW, you should not have the item description field in the table underlying the subform since the description should never change. (If you need to change it, you would only change it in the item table).

For the calculation, you would have to add a new, unbound (tied tied to a field in the subform's underlying table) control where you do the calculation QTY*ItemCost

[eskybel]

Ok, the TransItem-T table is updating so that looks good. As far as the item description, if I remove the item description from the table underlying the subform, how do I keep the description displaying in the subform, and how do I have the item description show in the transaction history for the POS? Can I just have it call that information based on the ItemID any time I run a report?

And as for the calculations, maybe this wasn't the right way, but essentially, here's how I did it, and it seems to be working appropriately.

I added this line below the dlookups under the ItemID After Update:

Me.ItemQty = 1

Then, I added this line in the ItemQty After Update:

Me.ItemCost = DLookup("ItemCost", "[ItemInfo-T]", "ItemID=" & Me.ItemID) * Me.ItemQty

So effectively, any time an item is entered, I have a line making the ItemQty default to 1. Then if the ItemQty is updated, it takes that line, and just runs the math, updating the ItemCost, and it's done. If that is not ideal, I'd definitely like to fix it.

Also, now I'm at the point where I am trying to calculate the TransSubTotal, TransDiscount, TransTax, and TransTotal values for the entire sale, but I seem to be getting no output. I tried to make this line:
Me.TransSub = Me.ItemCost

and realized I wasn't sure how it would gather "all" the line's ItemCosts to make one sub total. Then, I realized in the table that each item added has its own "Sub, Tax, Discount, Total" fields in the table, even if they aren't populated. What's the ideal way to total these up and run some more math on them, obviously, having the TransItems table hold all the totals as well?

Sorry for all the questions. But I appreciate the knowledge!

[jzwp11]

And as for the calculations, maybe this wasn't the right way, but essentially, here's how I did it, and it seems to be working appropriately.

No need to do it in code. Just set the control's control source to the expression =itemcost*qty (use the expression builder to help you out if needed)

As far as the item description, if I remove the item description from the table underlying the subform, how do I keep the description displaying in the subform

You would just remove the field name from the control source of the control that holds the description thus making it unbound. If you are using the subform in datasheet view, only the current record (usually the first record) will show the correct info in the bound fields, but not the unbound fields.

Also, now I'm at the point where I am trying to calculate the TransSubTotal, TransDiscount, TransTax, and TransTotal values for the entire sale, but I seem to be getting no output. I tried to make this line:
Me.TransSub = Me.ItemCost

and realized I wasn't sure how it would gather "all" the line's ItemCosts to make one sub total. Then, I realized in the table that each item added has its own "Sub, Tax, Discount, Total" fields in the table, even if they aren't populated. What's the ideal way to total these up and run some more math on them, obviously, having the TransItems table hold all the totals as well?

As mentioned previously, you would not store totals and subtotals in a table; you would calculate & display them on the fly when needed in forms, queries & reports.

Regarding the tax, you said that each item (qty*cost) is taxed. Is there a tax rate that is applied to all items? Is that rate the same for all items or does it vary by item type?

[eskybel]

No need to do it in code. Just set the control's control source to the expression =itemcost*qty (use the expression builder to help you out if needed)

I see what you're saying. Ok great I'll do that.

You would just remove the field name from the control source of the control that holds the description thus making it unbound. If you are using the subform in datasheet view, only the current record (usually the first record) will show the correct info in the bound fields, but not the unbound fields.

Ok, but let's say I wanted to do a reprint of a transaction. Won't the description be missing from that reprint since it is not located in the table along with the other data, or am I missing how that would be included in the reprint report?

As mentioned previously, you would not store totals and subtotals in a table; you would calculate & display them on the fly when needed in forms, queries & reports.

Ok, I remember that. That probably sounds a little off to me because I haven't spent any time in building reports. I guess for one reason or another, I just thought a report would display the contents of a table in a pretty way and calculations were not part of the process.

Regarding the tax, you said that each item (qty*cost) is taxed. Is there a tax rate that is applied to all items? Is that rate the same for all items or does it vary by item type?

The way I envision it is for sales done in the current state, there will be a fixed state tax. If done over the internet to a different state, as an example, there would be no sales tax (of course...that may change in the future). Maybe a command button that will enter the tax into the sale, or a checkbox stating "in state" or something?

[jzwp11]

Ok, but let's say I wanted to do a reprint of a transaction. Won't the description be missing from that reprint since it is not located in the table along with the other data, or am I missing how that would be included in the reprint report?

You would base your report on a query that brings in the item table and hence the description. You would use the cost field in the transaction table since that represents the cost at the time of the transaction. You would include any necessary calcs in your report as needed.

The way I envision it is for sales done in the current state, there will be a fixed state tax. If done over the internet to a different state, as an example, there would be no sales tax (of course...that may change in the future). Maybe a command button that will enter the tax into the sale, or a checkbox stating "in state" or something?

You can add a new table that lists the state and the appropriate tax rate for each.

tblStateTax
-pkStateTaxID primary key, autonumber
-txtStateName
-spTaxRate


I assume that you would tie the client (or the transaction itself) to the state somehow.

tblClient
-pkClientID primary key, autonumber
-txtClientName
-txtAddress
-txtCity
-fkStateTaxID foreign key to tblStateTax foreign key to tblStateTax

Once you have the above worked in, you should be able to pull the appropriate tax rate in when you need it.

As to subtotals on a report, you would have to set up a grouping level based on the transaction (I assume). In a form, you would add a control to the form footer for the item subtotal. You should be able to use the expression builder to help out.

[jzwp11]

I decided to work up a sample database that illustrates how to add the subtotals/totals to a form & report. The DB is attached

[eskybel]

Thank you again. I will dig into this again hopefully this weekend.