Access VBA help for a beginner

[mauriceglynn]

Hi

I have (am) taught myself VBA for MS Access but not real well and do not have a programming background. I hope you may be able to help...

first question... I have the following code:

Dim db As Database
Dim qrystrBookingStatus
Dim rstBookingStatus As Recordset
Dim varStatusItemStatus As String

Set db = CurrentDb

'MsgBox "varCurrentStatusField - " & varCurrentStatusField

' Check the current booking status value - true or false
qrystrBookingStatus = "select KitSent from tblBookingInfo" & _
" where BookingId = " & Chr(34) & Me.BookingId & Chr(34) & ";"
Set rstBookingStatus = db.OpenRecordset(qrystrBookingStatus, dbOpenDynaset)
varStatusItemStatus = rstBookingStatus!KitSent
rstBookingStatus.Close

----

you will notice the hardcoded field "KitSent" in the select statement.

I would like to make this code a subroutine that I can call and pass the field as a variable such as:

Dim db As Database
Dim qrystrBookingStatus
Dim rstBookingStatus As Recordset
Dim varStatusItemStatus As String
Dim fieldvariable as string

Set db = CurrentDb

'MsgBox "varCurrentStatusField - " & varCurrentStatusField

' Check the current booking status value - true or false
qrystrBookingStatus = "select " & fieldvariable & " from tblBookingInfo" & _
" where BookingId = " & Chr(34) & Me.BookingId & Chr(34) & ";"
Set rstBookingStatus = db.OpenRecordset(qrystrBookingStatus, dbOpenDynaset)
varStatusItemStatus = rstBookingStatus!fieldvariable
rstBookingStatus.Close

---

but then when I try and asign the retrieved value from the query (i.e. varStatusItemStatus = rstBookingStatus!fieldvariable) VBA does not use the value of the variable fieldvariable but takes it literally!

How do I do this?

Many thanks in advance.

MG

[Tjoppie]

Hey MauriceGlynn,

Are you using this code in a form. Do you want to specify the value inside a textbox n a form? Or how do you want to specify the value? (Kitsent)

[mauriceglynn]

Hi Tjoppie,

No I do not want to display the info in a form.

Basically I have a row of tick boxes (true/false) along the top of a form.

Clicking on any tick box calls an event procedure (on click). Each tick box has a seperate event procedure in which a set of corresponding variables are set including the correct field to address/query.

After setting the variables the first thing I want to do is work out if the tick box is being set to true or false so I see what the value is by querying the table at the on click event - if the tick box is false then it is being set to true.

I would like to check the status of any tick box by using the same routine called from each on click routine and having the field passed as an argument!

Does that make sense?

There is probably an easier way to know what the current status of a tick box is at an on click event? That would be simpler!

With anticipation.

MG

[Ecniv]

Put this code into the on_click event of a check/tickbox named chkIsThisTicked. Set the default value to false. Make sure it is not bound to a field.

msgbox iif(chkIsThisTicked,"Yes","No")

[Ecniv]

Dim db As Database
    Dim qrystrBookingStatus
    Dim rstBookingStatus As Recordset
    Dim varStatusItemStatus As String
    Dim fieldvariable as string

    Set db = CurrentDb

'MsgBox "varCurrentStatusField - " & varCurrentStatusField

' Check the current booking status value - true or false
    qrystrBookingStatus = "select " & fieldvariable & " from tblBookingInfo" & _
" where BookingId = " & Chr(34) & Me.BookingId & Chr(34) & ";"
    Set rstBookingStatus = db.OpenRecordset(qrystrBookingStatus, dbOpenDynaset)
    varStatusItemStatus = rstBookingStatus(fieldvariable)
    rstBookingStatus.Close

Try that?


You can access recordset fields in the following ways:
rst![field]
rst("field")
rst(<ordinal number of field>)
rst.fields("fieldname")
rst.fields(<ordinal number of field>)

"fieldname" is a string, so you can put a variable in there, or you can pass a number instead - your choice. String is more accurate if the field order is likely to change.

[Tjoppie]

I second that

[mauriceglynn]

many thanks, appreciated.

Maurice