Accessing a Value from a Form's Underlying Control Source

[whdyck]

I'm using MS Access 2003.

I have a form whose underlying Control Source (query) returns the column "TugArchiveDetailId". From within VBA, should I be able to access the value stored in this field and make decisions based on it?

I can access the value if I put it on the form as an invisible textbox, but not when accessing the column value directly.

Weird thing is that I have another column on the same record that I *can* access directly. I've double- and triple-checked the name of the columns.

Here's the code:

Private Sub cmdRestageRecord_Click()
On Error GoTo ErrorHandler
 
Dim lngTugArchiveDetailId As Long
 
lngTugArchiveDetailId = TugArchiveDetailId
MsgBox "CSR Archive ID = " & CsrArchiveDetailId & vbCrLf & "TUG Archive ID = " & TugArchiveDetailId
 
CleanUpAndExit:
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    Resume CleanUpAndExit
End Sub

This command button is on each record of a continuous form.

In the above code, the debug line

lngTugArchiveDetailId = TugArchiveDetailId

says that the value is zero, whereas the corresponding textbox displays '1'.

The message box displays:
CSR Archive ID = 1
TUG Archive ID =

I'd prefer not to create invisible fields if it's unnecessary.

Any idea where I'm going wrong?

Thanks for any help you can give.

Wayne

 

[mdlueck]

I can access the value if I put it on the form as an invisible textbox, but not when accessing the column value directly.

I would think this the standard operating methodology.

Weird thing is that I have another column on the same record that I *can* access directly. I've double- and triple-checked the name of the columns.

This scenario makes my head ache a bit...

Does perhaps the Form when bound to a DAO.QueryDef object have knowledge of the fields of the DAO.QueryDef object, and you can access those fields directly from VBA without having to bind them to Form Field controls? In order to proove this, I would think you would need to investigate the form object in the Watches and see if deep inside the Form object is the DAO.QueryDef data source and all of that DAO.QueryDef's fields.

That would be an interesting capability.

 

[boblarson]

You would need to use:

lngTugArchiveDetailId = Me!TugArchiveDetailId

 

[mdlueck]

In the Watches window watching the form Me object, look under Me\Recordset\Fields and also confirm Me\RecordSource. The RecordSource should be the name of your DAO.QueryDef object, and the ...\Fields should include each field the DAO.QueryDef included in its SELECT statement.

 

[whdyck]

You would need to use:

lngTugArchiveDetailId = Me!TugArchiveDetailId

Thanks, Bob! Works great!