Using a query's parameter value later in a form's control

I MUST do it this way, please help me figure out how.

I know there's a way to create a module or use querydefs or something but I'm new enough that this will be the first time I've attempted this.

I need to run a parameter query using the SQL string as created in the vba sub (used as the record source for a form.
The value for the query's parameter needs to be stored and accessed later in the same sub to filter the results of a combo box to the same recordset.

Private Sub cmdWellByLease_Click()
    
    Dim Str As String
    Str = "SELECT WRDB_WELLDATASHEETFINAL.* FROM WRDB_WELLDATASHEETFINAL WHERE (WRDB_WELLDATASHEETFINAL.WELLNAME like [Enter Beginning of Lease Name] & ""*"");"
        
    DoCmd.OpenForm "frmWellDataSheet", acNormal, Str, , acFormEdit, acWindowNormal
        
    'unhide WFLAC objects (requery command button and combo box)
    Forms!frmWellDataSheet!cmdRequery.Visible = True
    Forms!frmWellDataSheet!OtherSearch.Visible = True
    
    'Set filter for the combo box as the same parameter as given by the user above in the parameter query
    Forms!frmWellDataSheet!OtherSearch.RowSource = Str
    
End Sub

Open in new window

culpeesAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You could capture it first using the Access InputBox method, then use that captured value to open your form and set your Combo's Rowsource:
Dim sLeaseName As String
sLeaseName = InputBox("Enter Beginning of Lease Name: ", "Enter Lease Name")

Str = "SELECT WRDB_WELLDATASHEETFINAL.* FROM WRDB_WELLDATASHEETFINAL WHERE (WRDB_WELLDATASHEETFINAL.WELLNAME like '" & sLeaseName & "*'");"
        
    DoCmd.OpenForm "frmWellDataSheet", acNormal, Str, , acFormEdit, acWindowNormal
        
    'unhide WFLAC objects (requery command button and combo box)
    Forms!frmWellDataSheet!cmdRequery.Visible = True
    Forms!frmWellDataSheet!OtherSearch.Visible = True
    
    'Set filter for the combo box as the same parameter as given by the user above in the parameter query
    Forms!frmWellDataSheet!OtherSearch.RowSource = Str

Open in new window

0
 
PatHartmanCommented:
You have to include the parameter in the Select clause also.  Since you are using spaces in the name, be very careful to copy and paste the name so you don't mess up the spaces.

Str = "SELECT WRDB_WELLDATASHEETFINAL.*, [Enter Beginning of Lease Name] AS InputParm FROM WRDB_WELLDATASHEETFINAL WHERE (WRDB_WELLDATASHEETFINAL.WELLNAME like [Enter Beginning of Lease Name] & ""*"");"
0
 
culpeesAuthor Commented:
Pat, I added the parameter to the Select clause but it still breaks it. The Database fails and closes.

Scott, I get an end of statement compile error at the end of the Select statement at the closing parenthesis.

I believe the problem is with this specifically:
Forms!frmWellDataSheet!OtherSearch.RowSource = Str

The rest of the code works if you leave this out, this is where the database breaks.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
PatHartmanCommented:
Are you getting an error message?  If you are being prompted to enter a second value, then the two variables are not identical.
0
 
culpeesAuthor Commented:
No I'm not being prompted twice.  After the code hits the combobox.rowsource line the database closes and says that it must close and it will repair and create a backup.

I'm trying something else now. I'm trying to use the InputBox to hold the parameter value and put it in a form's text box for the combo box to look at. It's the redneck way around I guess.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
After the code hits the combobox.rowsource line the database closes and says that it must close and it will repair and create a backup.
That sounds like corruption. At worst, the code I suggested should just not work, or throw an error.

I'd suggest you make a backup, and then Compact the database. After doing that, compile it - open the VBA Editor, click Debug - Compile, and fix any errors. Continue doing that until the menuitem is disabled.

You might also Decompile the database. To do that, create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that, then compile again, then compace/repair.

Also never hurts to move everything over to a new, blank database. That tends to remove any container corruption, if there is any.

Of course, if you have a corrupt form these methods may not resolve that. If you do this and still find that the code errors out, try removing the combo and re-inserting it.
0
 
culpeesAuthor Commented:
Gosh. I was hoping it was just that code. I have already been through the slew of fixing a corrupted database. This is supposed to be the fixed one!

I even removed the module "YES" property from all the forms and then manually pasted the code back into each form after creating my new database.

Thanks anyway. I've redone my code now so that it seems like it really should work but keeps closing upon : Me!WellSearch.requery.

The only thing the code is not doing is refreshing the combo box to filter to a similar selection of records as seen in the form.

Private Sub cmdRequeryName_Click()
On Error GoTo Err_cmdRequeryName_Click
   
    Me!FilterBox.Value = Null
    
    If Me!FilterType.Value = "LEASE" Then
        Dim Criteria As String
        Dim stLinkCriteria As String
        Criteria = UCase(InputBox("Enter Beginning of Lease Name: ", "Search by Lease"))
        stLinkCriteria = "[WELLNAME] LIKE " & "'" & Criteria & "*'"
    
        DoCmd.OpenForm "frmWellDataSheet", acNormal, , stLinkCriteria
        Me!FilterBox.Value = Criteria
    
        'unhide WFLAC objects (requery command button and combo box)
        Me!cmdRequeryName.Visible = True
        Me!WellSearch.Visible = True
        
        Me!WellSearch.Requery
    Else
        DoCmd.Requery
    End If
    
Exit_cmdRequeryName_Click:
    Exit Sub

Err_cmdRequeryName_Click:
    MsgBox Err.Description
    Resume Exit_cmdRequeryName_Click
    
End Sub

Open in new window


Alas, Apparently I'm going to be doing more corruption exercises. This is one full week of dealing with this now.
0
 
culpeesAuthor Commented:
The InputBox function worked well. That did the trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.