Solved

How to pass a filter value into text box on form using VBA code

Posted on 2003-11-06
9
509 Views
Last Modified: 2012-05-04
I have a form with a button on it that opens another form and passes a string value to it. On the 2nd form, I have an add new record button and I need to update a field ([Site]) on the "new record" form with the filter'd string value. Here's what I have now but the problem is it passes the whole filter string and not just the value that was used to filter the form. I have been stuck on this for a couple hours. Help! Access 2000.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stLinkCriteria As String

    stLinkCriteria = Me.Filter
             
                   
    DoCmd.GoToRecord , , acNewRec
   
    Me![Site] = stLinkCriteria
    Me!LotID.Locked = False

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
   
End Sub
0
Comment
Question by:amkbailey
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9697042
' passes a string value to it'
Is this relevant to the question?
If it is, then what does contain (example please).

Pete
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9697189
so you want to give you new record the parameter of the Filter ?

how can you be sure, that the user has not filtered twice ?
on which field has te user filtered ?

waht you can do: Extract the last piece of the filter-string with the Split function and take the max-Index, which should give you the last "word" inthe filter string

regards, Franz
0
 

Author Comment

by:amkbailey
ID: 9697191
A string is passed to the second form, for instance "Woodview Estates" and if there are any records that are related to it, they are filtered on the second form. If there are no records or they need to add a new record, they select the add record button and I want the value "Woodview Estates" to update a field on the form called "Site". The value "Woodview Estates" is visible in the properties of the form under filter [Site]='Woodview Estates'

I want the site field to contain Woodview Estates and not [Site]='Woodview Estates' like it contains now.
0
 
LVL 23

Accepted Solution

by:
heer2351 earned 250 total points
ID: 9697206
I would pass the string value as an open argument so you can use it again:

DoCmd.OpenForm "yourFormName", OpenArgs:="string_value"

on the form:

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

    DoCmd.GoToRecord , , acNewRec
   
    Me![Site] = Me.OpenArgs
    Me!LotID.Locked = False

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click    
End Sub


0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:amkbailey
ID: 9697213
Yes, I want to give the field [Site] on the new record the parameter of the filter.

Users will not be allowed to filter on their own. The button on the first page is automatically filtering the 2nd form for them.

Can I use the split function to pull out the text that I want ex.

Woodview Estates

0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9697234
if this will be constant, you can extraxt tha directly:

stLinkCriteria = Mid$(Me.Filter,9,Len(Me.Filter)-9)

maybe the last should be 10 or 8 , just look

Regards, Franz
0
 

Author Comment

by:amkbailey
ID: 9697270
The filter value won't always be the same. But the form will always be filtered by only once when it is pulled up. It all depends on what record the first form is on. If they happen to be on a different record on the first form, for instance "Valley View Estates", that value will be what is passed as the filter to the second form when they click the button.

Again though, the 2nd form is only filtered once based on the first form and the end users will not be allowed to set their own filters.
0
 

Author Comment

by:amkbailey
ID: 9697369
Thanks for the help. It was probably an easy one but I don't do enough VBA as I should be with Access. Here is the code for the first form.

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim sitepass As String
   
    stDocName = "frmAddLots"
   
    stLinkCriteria = "[Site]=" & "'" & Me![SiteName] & "'"
    sitepass = Me![SiteName]
   
    DoCmd.OpenForm stDocName, , , stLinkCriteria, OpenArgs:=sitepass

AND THE SECOND FORM

DoCmd.GoToRecord , , acNewRec
   
    Me![Site] = Me.OpenArgs
    Me!LotID.Locked = False
0
 
LVL 4

Expert Comment

by:inox
ID: 9697664

you can assemble your forms filter by a condition and a value, i.e:
 
  Filtervalue=100
  me.filter="YourField >= " & Filtervalue

later you use
Me![Site] = Filtervalue
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now