Confused about Expression Builder (SELECT INTO, or INSERT INTO. With WHERE) (1 Viewer)

[ConfusedA]

I wasn't sure if I should post this here or in the code section.

Relevant Info: Using Access2003, connected to: http://www.access-programmers.co.uk/forums/showthread.php?t=174345

What is involved:
3 tables.
1 Form.
Tables
Tableprice:
Item Quantity Current Price(which changes based on additions)

TableAddition:
Item Quantity Added Price of New Quantity
TableOutgoing:
Item Quanity Removed etc. etc. Price at time of use (needs to be taken from tableprice)
Form
FormChanges; includes many fields. Relevant ones are: Text box for amount of product used, list box for the item used, and a locked text box for the current price.


What I need: to keep a record of each outgoing transactions 'current price' which is stored in a seperate table (tableprice) from the table of transaction histories (tableoutgoing).
What I have tried: Multiple attempts in the event expression builder in the object properties of the text box i've created to display the current price (contol source is tableoutgoing. so that the data may automatically be placed into the record that the other information given in the form is going to)
Stuff like:
*In the text boxe's properties under Event...through expression builder.
SELECT fldprice
INTO tableoutgoing
FROM tableprice
WHERE flditem = fromchanges.lstitems
**with and without square brackets have produced errors.


If anyone can give me a helping hand on this it would be appreciated, I've spent most of the morning on this, I have read through many websites but have been unable to find what I am looking for.

Thanks,
ConfusedA

 

[ConfusedA]

Ok, how about this....there's a syntax error here, I can't see it. Any ideas?

SELECT [fldpricerperunit]
FROM [tblGas/Diesel]
WHERE [fldtype] =[Forms]![frmGas/Diesel Update]![lstGorD]

 

[boblarson]

Try this (if fldtype is text):

SELECT [fldpricerperunit]
FROM [tblGas/Diesel]
WHERE [fldtype] = & Chr(34) & [Forms]![frmGas/Diesel Update]![lstGorD] & Chr(34)

 

[ConfusedA]

Thanks for the thought! Unofrtunately, the macro builder is still telling me it is unable to parse type/lst are text.

 

[Scooterbug]

I Dont use the expression builder...but you could do a dlookup.

Price = dlookup("[fldpriceerperunit]","tblGas/Diesel","[fldtype]=" & [forms]![frmGas/Diesel Update]![lstgorD])

Also, not a good idea to use / in names. (Or spaces for that matter, IMHO).

 

[boblarson]

Thanks for the thought! Unofrtunately, the macro builder is still telling me it is unable to parse type/lst are text.

Macro builder? Do you mean the QBE grid instead?

 

[ConfusedA]

Oh good point Bob, this would be much easier done through a query than what I was thinking of doing. Thanks!

So, if I use a query all I would have to do is make the criteria like "*<the field for item type>*" and then grabbing the price. How would I then make the price the result in the text box for price(the control source is the field that needs to get this price information), or should I use an append query to just add the price value into that record line?

Thanks.
ConfusedA

 

[ConfusedA]

Ok, so I managed to get this to work with an update query, and it appears to work fine, but I have a new problem now. When I click on my button to update, it tells me that multiple records will be updated (more than I have in the table currently) how is this possible?

 

[ConfusedA]

RE: the problem is that it is updating every record in the table now.

 

[boblarson]

I may have asked this question before (I can't remember) - Any chance you can post the database?

 

[ConfusedA]

Update: I've managed to get the update query to differentiate between types of item, but the update still changes the price of all of the same items when I only want it to place the cost in to the current record being submitted. Any thoughts?

 

[ConfusedA]

I'm unable to post the database, part of it has some confidential data and even without that it's really big.

 

[boblarson]

Okay, not sure I can get it without seeing (I'm more visual and hands on - I can come up with the answer quickly if I can actually see and play with it).

Can you post the current SQL you have?

 

[ConfusedA]

Here is what I have with my Gas/Diesel part of my inventory DB, the inventory used will always be entered on a week-month-year basis, for G/D it will be based off of Trucks and Jobs as well. I use a select query to make sure that the record being updated should be a single record. I do this by having a command button that saves and then runs the update query. So all the values in my select query are Like [Forms]![Formname]![Field-object]. Which allows me to match up by date (week/month/year), Truck, Odometer reading and amount driven. The problem is, it is not recognizing odomether reading and amount driven.

:::SOLUTION::: When I started writing this, I was intent on explaining the problem, but by writing this I realized that the issue was a lack of relationships between the values used in the update query.

Thanks for all the help! It now works like a charm!

 

[boblarson]

:::SOLUTION::: When I started writing this, I was intent on explaining the problem, but by writing this I realized that the issue was a lack of relationships between the values used in the update query.

Thanks for all the help! It now works like a charm!

That's what most Psychiatrists and Therapists do. They have you talk things out and pretty much solve your own problem. So, that happens frequently on the forums too. You can be having to explain something so deeply that all of a sudden it dawns on you what is going on.

Glad that worked out for you.