CODEWITHSUNDEEP
vbams-access
user3435209
user3435209

Reputation: 23

How to set a variable to a field value retrieved in a query

Updated 3/14/19 to include code for setting variable and Run-time error is '2427' 'You entered an expression that has no value.' on setting outCome1 variable

Private Sub Report Open(Cancel As Integer)    
    Dim outCome1 As String  
  ' Set public variable to true to indicate that the report    
  ' is in the Open event  
    bInReportOpenEvent = True  
    MsgBox (bInReportOpenEvent)  
  ' Open Appt Query Dialog  
    DoCmd.OpenForm "craid CMM Client Report Dialog", , , , , acDialog  
    Cancel Report if User Clicked the Cancel Button  
    If IsLoaded("craid CMM Client Report Dialog") = False Then Cancel = True
    outCome1 = (Reports("CMM Client Status Report").Controls("googleoutcome").Value)    
    MsgBox (outCome1)  

  ' Set public variable to false to indicate that the  
  ' Open event is completed  
  '  bInReportOpenEvent = False  
End Sub

Also, I notice when I run that bInReportOpenEvent does not stay True when in Dialog Form and so it doesn't appear to be public even though I have run Sub Report Open with Public and it still doesn't stay True when in Dialog Form.

I am new, but have been struggling quite a bit with trying to set a variable to a field value retrieved in a query, so thought I would see if I can get some relief.

I am writing an Access Report that uses a Dialog Form to prompt user for which record in database to display on Report.

I use a query as the Record Source for the report. One of the fields I retrieve with the query is googleoutcome. I want to set a variable to the value of googleoutcome, so I can then do a test on it and display the result on my report.

I have tried:

Dim outCome1 As String  
outCome1=Me!googleoutcome  

Dim outCome1 As String  
outCome1=[googleoutcome]  

Dim outCome1 As String  
outCome1=Me.googleoutcome

I am receiving the error:

error '2427; You entered an expression that has no value.

Any help is appreciated.

Laura

Upvotes: 1

Views: 1143

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

In VBA, the Me keyword refers to an instance of the class in which the current code is executing. For example, in MS Access, if you use Me within a Form Module, it will return an instance of the active Form; if you use it within a Report Module, it will return an instance of the active Report.

Therefore, your use of Me in the On Open event handler for the Report is returning an instance of the Report which is being opened, not the Form you have opened.

Instead, you could reference the value of the form control by traversing the object model in the following way:

Forms("YourFormName").Controls("YourControlName").Value

Upvotes: 1

Related Questions