Solved

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

Posted on 2014-04-21
8
274 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 36

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 84

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 36

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 84
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

828 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