Load Variables With Current DB Data

[zipur]

OK,

I have not done much with Access 2000 VB so here is the problem:

I want to create a variable in VBA that for example loads the sum of a column in a table.

Sounds simple, I know, but I am missing something.

Any help is appreciated!!!

 

[scriverb]

You must make a declaration of the variable name and type:

Dim vSumOfColumn as Long

Then the assignment of the value to the variable using the DSum function:

vSumOfColumn = DSum("[tblFieldName]", "tblYourTableName&quot

This will SUM all of the values in tblFieldName name in tblYourTableName and assign that value to the variable vSumOfColumn.

Now you didn't say if you wanted all records to be selected and added together. There is a third parameter of the DSum function that is the criteria statement. This is used to select only certain records meeting a pre set value. The following statement adds that type of criteria statement to the function. Note that this is looking for records with the table field = 1000. This is how a numeric criteria statement is setup:

vSumOfColumn = DSum("[tblFieldName]", "tblYourTableName", "[tblFieldNameXX]= 1000&quot
An alphanumeric is slightly different. Single quote marks must surround the value being compare to:

vSumOfColumn = DSum("[tblFieldName]", "tblYourTableName", "[tblFieldNameXX]= 'Robert'&quot

I hope this helps you understand this process.

Bob Scriver

 

[zipur]

Ok, that gives me some ideas... but what I really need to know is the proper way to load variables from the tables for use in calculations/field population/inserts/etc.

Since my post I have figured out:

Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Rs1 = New ADODB.Recordset
SQLCode = "SELECT LTicketNum From TransIn"
Rs1.Open SQLCode, CurrentProject.Connection
Rs1.MoveFirst

Dim tester As Integer
Do Until Rs1.EOF
tester = Rs1!LTicketNum
MsgBox (tester)
Rs1.MoveNext
Loop

Rs1.Close
Set Rs1 = Nothing

--- not sure if this is the best way or not. I am used to Server Side Programming and used to do VB6 a few years ago. VBA seams to be just different enough to be anoying.

Thanks for the help

 

[TrojanRabbit]

In the case of summing the column or other column functions like that, a query is probably the best way to go. The only way I know of to do it in VBA is by traversing the records using a recordset object. You can use a totals query to find the sum, then get the value from the query. You may need a recordset object to get it from the query, but you may be able to refer to the query directly.

 

[TrojanRabbit]

Here's a quick way to do it.
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT SUM(uh3) FROM uh&quot
<variable> = rst.Fields(0).Value
rst.close

You can also use the CreateQueryDef method (something like that).

 

[zipur]

Cool, I tried this and works great!! Thanks Much!


Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Rs1 = New ADODB.Recordset
SQLCode = &quot;SELECT COUNT(LTicketNum) As test From TransIn&quot;
Rs1.Open SQLCode, CurrentProject.Connection
Rs1.MoveFirst
Dim tester As Integer

tester = Rs1!test
MsgBox (tester)

Rs1.Close
Set Rs1 = Nothing