Slight VBA Problem

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I've got the following:
Private Sub Dealer_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim srtMsg As String

srtMsg = "" & NewData & "'is not an avaialable Dealer Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add the new Dealer Name to the list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to
re-select."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Dealer Name?") = vbNo
Then
Response = acDataErrContinue
Else

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Dealer_Info", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Dealer_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If
DoCmd.OpenForm "frm_Dealer_Info"
Forms!frm_dealer_info.FilterOn = True
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Everything seems to work fine, except for the following portion:

DoCmd.OpenForm "frm_Dealer_Info"
Forms!frm_dealer_info.FilterOn = True

the Forms!frm_dealer_info filter is based on the value that the user has
just entered within a combobox on the form that triggered this not in list
event. My problem is that the filter only sees the previously entered value
not the new value. I guess this issue meets the criteria cited on
http://www.mvps.org/access/forms/frm0008.htm unfortunately I can't use this
method because the form that is being called to open contains all of the new
dealer's contact info and after it is updated another script runs to
populate fields within the Orders Form.

I guess may question is; can I committ the current value somewhere/somehow
using a command within the above code?

Thanks!
Anthony
 
Anthony Viscomi said:
I've got the following:
Private Sub Dealer_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim srtMsg As String

srtMsg = "" & NewData & "'is not an avaialable Dealer Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add the new Dealer Name to the list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to
re-select."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Dealer Name?") = vbNo
Then
Response = acDataErrContinue
Else

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Dealer_Info", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Dealer_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If
DoCmd.OpenForm "frm_Dealer_Info"
Forms!frm_dealer_info.FilterOn = True
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Everything seems to work fine, except for the following portion:

DoCmd.OpenForm "frm_Dealer_Info"
Forms!frm_dealer_info.FilterOn = True

the Forms!frm_dealer_info filter is based on the value that the user has
just entered within a combobox on the form that triggered this not in list
event. My problem is that the filter only sees the previously entered value
not the new value. I guess this issue meets the criteria cited on
http://www.mvps.org/access/forms/frm0008.htm unfortunately I can't use this
method because the form that is being called to open contains all of the new
dealer's contact info and after it is updated another script runs to
populate fields within the Orders Form.

I guess may question is; can I committ the current value somewhere/somehow
using a command within the above code?

Thanks!
Anthony
Click to expand...

Why not pass the new data to "frm_Dealer_Info" in the OpenArgs property?
 
I tried that, but due to the fact that the "not in list" value that the user
enters within the combo box has yet to be committed, the filter only
recognizes the previously entered value.
 
Anthony Viscomi said:
I tried that, but due to the fact that the "not in list" value that the user
enters within the combo box has yet to be committed, the filter only
recognizes the previously entered value.
Click to expand...

The point of my suggestion was that you could create and apply the filter in
the Open event of frm_Dealer_Info. But, now I look at it again, that isn't
necessary. You don't need to use a filter at all, just open frm_Dealer_Info
with an appropriate WhereCondition i.e.

DoCmd.OpenForm "frm_Dealer_Info", WhereCondition:="DealerName=""" & NewData
& """"
 
Thanks!
Brian said:
The point of my suggestion was that you could create and apply the filter
in
the Open event of frm_Dealer_Info. But, now I look at it again, that
isn't
necessary. You don't need to use a filter at all, just open
frm_Dealer_Info
with an appropriate WhereCondition i.e.

DoCmd.OpenForm "frm_Dealer_Info", WhereCondition:="DealerName=""" &
NewData
& """"
Click to expand...
 
Back
Top