Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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

0
culpees
Asked:
culpees
  • 4
  • 2
  • 2
1 Solution
 
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Industry Leaders: 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!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now