amkbailey
asked on
How to pass a filter value into text box on form using VBA code
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
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
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
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
ASKER
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.
I want the site field to contain Woodview Estates and not [Site]='Woodview Estates' like it contains now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
if this will be constant, you can extraxt tha directly:
stLinkCriteria = Mid$(Me.Filter,9,Len(Me.Fi lter)-9)
maybe the last should be 10 or 8 , just look
Regards, Franz
stLinkCriteria = Mid$(Me.Filter,9,Len(Me.Fi
maybe the last should be 10 or 8 , just look
Regards, Franz
ASKER
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.
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.
ASKER
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
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
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
Is this relevant to the question?
If it is, then what does contain (example please).
Pete