Solved

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

Posted on 2014-04-21
8
276 Views
Last Modified: 2014-04-23
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

0
Comment
Question by:culpees
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40013321
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40014536
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
 

Author Comment

by:culpees
ID: 40015377
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 37

Expert Comment

by:PatHartman
ID: 40015651
Are you getting an error message?  If you are being prompted to enter a second value, then the two variables are not identical.
0
 

Author Comment

by:culpees
ID: 40015763
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
 
LVL 85
ID: 40015933
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
 

Author Comment

by:culpees
ID: 40017641
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
 

Author Closing Comment

by:culpees
ID: 40017652
The InputBox function worked well. That did the trick.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which ā€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pā€¦

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question