SQL In Access

[Gliff]

I've been trying to find a solution to this but it's a pretty tricky thing to find a standard code for. It's not the most complex of problems though, so hopefully someone can help.

I have a table in Access which is for Ordering from a shop. I have a field named TotalCost which is to be calculated using a query, so that the Quantity in the Order Table is multiplied by the cost in the Product Table.

From what I gather, I can't store the result of this calculation back into the TotalCost field. It also seems that neither Update Queries nor Append Queries work. It also won't allow me to make the TotalCost a lookup field.

I presume the method of getting the result of the calculation in the query to the TotalCost field is through SQL in the Query ... but I have no idea how I'd go about this. Any help would be appreciated. Thanks!

[Sidewinder]

Seems that if the database is normalized, there will be a field common to both the OrderTable and the ProductTable. Assuming that the TotalCost field is in the OrderTable, you should be able to Set OrderTable.TotalCost = OrderTable.Quantity * ProductTable.Cost

Assuming the common field is something like ItemNumber, you would need to join the two tables on this field:

where OrderTable.ItemNumber = ProductTable.ItemNumber

Note: the two field names from each table need not be identically named, just contain the same data.

That said, it is not recommended that calculated fields be stored in the database. If either Quantity or Cost were to change, the TotalCost field would be in error.

 

[Gliff]

You're actually right about the Total Cost thing. I see your point about the Quantity and Cost changes entirely. I do, however, have another query.

This time, what i'm trying to do is use VBA in a Form. I have a ListBox, and what I'd like to do is display all Product Values from tblStock which have a Stock Level of 0 or less than 0. I'm not entirely sure what kind of SQL code I should be using here? Thanks!

[Sidewinder]

Not knowing the all the particulars, this is just a general method you might want to consider.

This SQL statement should produce a recordset:

Code: [Select]

Select value From tblStock Where StockLevel =< 0

After that it should be a simple matter to create a loop for reading the recordset and loading the listbox.

Code: [Select]

rs.movefirst
do until rs.eof
   lstBox.Add(value)
loop

Note: change value in the SQL statement and the recordset loop to the field you want in the listbox.

 

PS. rs is an arbitrary name I gave to the recordset.

[Gliff]

Aha! Right, now I understand. I've entered this code,

Code: [Select]

Dim rs As Recordset

Private Sub List8_BeforeUpdate(Cancel As Integer)
RunCmd.SQL ("Select Product From tblStock Where StockLevel =< 0")
rs.MoveFirst
   Do Until rs.EOF
   List8.AddItem (Product)
Loop
End Sub

But the problem is I now come up with a Runtime Error 424, and it says "Object Required"? Also, I'm usinf Access 2000 if that's of any relevance. List8.Add didn't work and the only other one on the list was .AddItem, but I presumed there wasn't much difference?

Thanks though!

[Sidewinder]

Yeah, you're right, Add should have been AddItem. 

About the object required error. What is RunCmd defined as? and where did the actual error appear?

Normally I just use the open method on the recordset and pass along the sql, connection string, and cursor flags:  rs.open "sql data", connectionstring

 

[Gliff]

At the moment, the error comes up when I click on the list Box. With regards to RunCMD, I haven't even defined it :-/ I've seen it on other SQL codes so thought that was the right thing to put in. Sorry, i'm OK at regular coding but no good at VBA or SQL!

[Sidewinder]

Don't know if this will work within Access, but you might use it as a guide:

Code: [Select]

Const adOpenStatic = 3
Const adLockReadOnly = 1

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim SQL as String

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
SQL = "Select Product From tblStock Where StockLevel =< 0"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fullpathtoyourdatabase"

Private Sub List8_BeforeUpdate(Cancel As Integer)
  rs.Open SQL, cn, adOpenStatic, adLockReadOnly
  rs.MoveFirst
  Do Until rs.EOF
    List8.AddItem (Product)
  Loop
  rs.Close
  cn.Close
End Sub

Make sure you replace fullpathtoyourdatabase with the appropriate data.

Tried to cover everything in a small space. I'm not sure how much Access predefines for the user, so there may be more code than required.

 

[Gliff]

Crumbs, I tried but I couldn't get it to work, sorry - it looks like the right type of code but my amateur expertise brings it down.

I have however stumbled across another way of doing this. One method is just placing a query which says all the stock levels which have less than 0 and then you add that to a form. The more interesting concept is this.

When you use a Form Wizard, if I make my Stock Table the thing it uses, and then open the form in Design View, it pre-determines the fields of the Stock Table. I can then do something like this ~

If StockLevel <= 0 Then List1.AddItem (Product)

The problem is not even that works Any ideas?

[Sidewinder]

When I looked at Access last night I realized you don't have to write any code! First design a query (use the wizard) to select records where StockLevel =< 0. Design your form with a listbox making sure to click: "I want the list box to lookup the values in a table or query". On the next screen, choose the query you designed as the data source for the listbox.

I'm still shell shocked at how simple this was, it was the ultimate KISS 

[Gliff]

That is a simple way of doing it! Thanks. I seem to be finding alot of easy ways out today with this database. I've been struggling with a validation code in a form and I realised that the problem I've had through this entire thread is because I need fields to be contained in objects like that List Box, within the form. It works so much easier (I need coding somewhere in this database for my CPT Project marks ) But still, that Qry makes things tons easier, Thanks!