Assigning variables in VBA

[kiplook]

I'm trying to assign a value of a variable in a table or query to a variable in a VBA Module. The reason that I'm doing it this way is because long and multiple calculations that follow so doing with macros or queries won't do the trick.

I keep getting errors.

The Table has the following fields:

Date
one
two
three
four
five

This is my code


////////////////////////////////////////////////////

Function StartSimulation()
On Error GoTo StartSimulation_Err


Dim xone As Integer
Dim xtwo As Integer
Dim xthree As Integer
Dim xfour As Integer
Dim xfive As Integer

DoCmd.OpenQuery "query1", acViewNormal, acReadOnly
DoCmd.GoToRecord acQuery, "Query1", acFirst

' With CodeContextObject
xone = !one
' End With

DoCmd.Minimize
DoCmd.OpenForm "Results", acNormal, "", "", , acReadOnly



StartSimulation_Exit:
Exit Function

StartSimulation_Err:
MsgBox Error$
Resume StartSimulation_Exit

End Function

///////////////////////////////////////////////////

With the 'With' statement in I get this error:

Microsoft access can't find the field 'one' referred to in your expression:

Without the 'With' statement I get this error:
Invalid or unqualified reference.

 

[Blorf]

Hi.

Your commands open the query, and likely displays it, but doesn't open it as a record set for your function.

Try something like

Dim X as object
dim MyVar as whatever

set X = currentdb.openrecordset ("Query1")
X.movefirst

while not X.eof()
MyVar = X.one
X.movenext
Wend

There are better ways of course, and if you don't need VBA, ie an update query will work or what not, that is usually faster.

Either way, I hope this helps.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[deekydoo]

Hi,

I've used recordset manipulation a lot. I wrote this function to return a pointer to a recordset:

[blue]Public Function gGetRecordset(strQueryName As String, _
Optional Direction As CursorTypeEnum = adOpenForwardOnly, _
Optional ReportError As Boolean = False, _
Optional LockType As LockTypeEnum = adLockOptimistic) As ADODB.Recordset

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

On Error GoTo Err
rst.Open strQueryName, CurrentProject.Connection, Direction, LockType

Set gGetRecordset = rst
Set rst = Nothing

Exit_Procedure:
Exit Function
Err:
If ReportError Then
Call gAddError("basUtils", "gGetRecordSet", Err)
gDisplayAnyErrors
End If

Set gGetRecordset = Nothing
Resume Exit_Procedure
End Function[/blue]


and here is a procedure which uses it:



[blue]Private Sub cmdExampleForTekTips_Click()
' gets a record set and then replaces JaneID with BillID in each relevant table entry filed PTContactID

Dim rst As ADODB.Recordset
Set rst = gGetRecordset("select PTContactID from instjobtodo where ptcontactid = " & JaneID, adOpenDynamic)
If Not gRstEmpty(rst) Then
With rst
.MoveFirst
While Not .EOF
!PTContactID = BillID
.MoveNext
Wend
.Close
End With
End If
End Sub[/blue]

Note:
1. you move about the recordset using the '.' and you access fields in the recordset using the '!'

2. gRstEmpty is a boolean function to test for an empty recordset (BOF & EOF = true)

Hope this helps

Deek

 

[kiplook]

I tried Blorfs teqnique and got the following error:

Too few parameters. Expected 2.

In trying to fix that I got the following error:

Data type conversion error.

I’m using access97. Any ideas on that?


I haven’t tried the one from Deekydoo yet.

 

[Blorf]

Hi.

My suggestion works best on Tables. Querys can some times give problems.

I would suggest deekydoo's, as his allows you to poke a select statement, or a query name into the connect string.

Make sure though that you have ado included in your references, or it too will not work.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[kiplook]

The table seems to be working for me however I can’t seem to control where it starts. It seems to start in the same place each to and then go in order (by date) from there. The date in my table range from 11/29/00 to 11/27/05 and no matter what I do it always starts at 8/29/01. Ultimately I want to be able to control where it starts from the form that starts the Module. Right now I’d settle for being able to control the start by writing the code in VBA.

 

[Blorf]

Hi. Assuming you are refering to my sample, the easiest method I suppose is creating an index on your table. Say you index your field "MyDate"


X.Index = "MyDate"
X.MoveFirst

That should do it.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[MajP]

Blorf's simple code should should work. However, a couple things to help.
replace

Dim X as object
 with
Dim rsSimulation as DAO.recordset

It will work as is, but Access can return either a "DAO.recordset or a ADODB.recordset", and this will be determined by the order of the refernces you have loaded. If you do not specify you are very likely to run in to problems. Not that it is necessary, but Reddick (Hungarian) naming conventions really help.
Also in the openrecordset method you can put in a table, query name, or a sql statement. So modifying Blorf's a little:

Dim rsSimulation as DAO.recordset
dim MyVar as whatever
dim strSql as string
strSql = "SELECT query1.* FROM query1 ORDER BY query1.one"
set  rsSimulation = currentdb.openrecordset (strSql)
rsSimulation.movefirst
do while not rsSimulation.eof()
   MyVar = rsSimulation.fields("one")
   rsSimulation.movenext
loop

In this case make sure you have a reference to Data Acess Object ver x.x.