Need some VB/MS Access help

[crokett]

If I have a SQL statement that goes something like:

SELECT afield, SUM ([bfield] * [cfield]) AS aTotal FROM sometable


can I have an Insert statement that goes:
INSERT INTO someothertable (aTotal AS dfield)

What I really want to do is perform some operations on aTotal before inserting into the second table. Can I do this as part of the INSERT statement?

[boblon]

Not sure if this is what you want, but this will take the value of bfield and cfield from sometable, multiply them and insert them into dfield of someothertable where the record ID in someothertable is equal to 1.

INSERT INTO someothertable ( dfield )
SELECT (sometable.bfield*sometable.cfield) AS Expr1
FROM sometable, someothertable
WHERE (((someothertable.ID)=1));

I tested this in Access 2000. Hope this helps.

BobL.

[crokett]

Bob,

That helps. What I really want is to be able to access a field in a table directly from VB.

Sorta like:

DIM somevar AS Integer
somevar = SomeTable.afield

Not sure how to do that. When I try I get an 'object not found' error. I tried creating a DAO Reference set and a Tableref but I don't think either of those will work. I didn't see how I could use that DAO objec to do what I want. Or is SQL the right way to do this and I am going through the back door to get in the front?

[boblon]

Well, I'm not sure where your going with this. I am assuming your operating wholly within the Access database. If your using VB in another app and want to call values from an Acess db this won't work, you have to establish a connection to the db first.

But anyhow, first make sure you have a table called 'sometable'. It needs to have at least one record with a numeric field named 'aField'. This field needs to contain a number.

The following code will get the value in aField, mulitply it by 2 and pop up a Message Box with the result.


Create a form and put a command button on it. Paste the following into the command buttons 'on click' event procedure:

Dim dbnew As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT aField FROM someTable"
Set dbnew = CurrentDb()
Set rs = dbnew.OpenRecordset(strSQL)
If Not rs.EOF Then
x = (rs!aField * 2)
MsgBox (x)
End If
rs.Close

While in the VB Code screen go to 'Tools/References' and make sure you have 'Microsoft DAO 3.6 Object Library' enabled (your version might be different).


Go back to the form and click on the button. A Message box should pop up showing double the value in aField.

Open the table and change the value of aField to ensure it is working.

I hope this helps cuz I really have no idea what your ultimate purpose is.

Good Luck.

BobL.

[Stan]

If I have a SQL statement that goes something like:

SELECT afield, SUM ([bfield] * [cfield]) AS aTotal FROM sometable


can I have an Insert statement that goes:
INSERT INTO someothertable (aTotal AS dfield)

What I really want to do is perform some operations on aTotal before inserting into the second table. Can I do this as part of the INSERT statement?

David,
Holler at me if you check in to the chat room later tonight or this weekend. I have 'oodles' of code that should help ya out, just need an idea of what your doing.

[crokett]

Bob,

Thanks. This is what I needed. Already have the recordset so now I know how to access the fields. This may be going in the back door to get through the front. What I am trying to do is generate an invoice. To do this I have 1 table with jobs and another with the items used on a particular job. They are linked on a job num. So I need to get the job num, select the items for that job out of the items, get a total $ for each item, then sum that for a sub total. Then I add taxes, subtract any discounts and finally stuff the info into an invoice table. So thinking was if I could get the fields in the table I could do my operations then insert the results. Conceptually easier for me to do than to use a SQL statement.

[boblon]

Ok, cool. Good luck.

BTW, one simple way to generate SQL if your not familiar with it is to use the query designer in Access (drag and drop), enter your criteria and what not, then select SQL view. Whalla, SQL for your code.

BobL.