Solved

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

Posted on 2014-04-21
8
275 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

737 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