Access Learner - Need Help with DoCmd.OpenForm to multiple criteria (1 Viewer)

[F.I.G.]

How to add second criteria to OpenForm function?
Basically, I need help with double click VBA code event in a ListBox.
I two related forms, in the primary form contain a listbox, when user double click on a specific record, the second form will open and should filter record based on 2 criteria (LoanInfoID and RequestDate).

Private Sub RequestList_DblClick(Cancel As Integer)
DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID AND SECOND CRITERIA IN LIST BOX(RequestDate)
DoCmd.GoToRecord , , acNewRec
End Sub

ListBox Row Source:
SELECT T_Request.LoanInfoID, T_Request.RequestDate, T_TransactionType.TransactionType
FROM T_Request INNER JOIN T_TransactionType ON T_Request.TransactionTypeID = T_TransactionType.TransactionTypeID
GROUP BY T_Request.LoanInfoID, T_Request.RequestDate, T_TransactionType.TransactionType
ORDER BY T_Request.RequestDate;

Thank you in advance
Van

 

[pbaldy]

Along the lines of:

DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.ListboxReference & "#"

 

[F.I.G.]

Thanks Paul for getting back, unfortunately I am still getting the same error.

Please see attached.

Regards,
Van

 

[John Big Booty]

Which column of ListboxReference holds the date you are looking for?

 

[F.I.G.]

Which column of ListboxReference holds the date you are looking for?

Including the hidden/invisible one, RequestDate would be would in column number 2. There are 3 columns in total.
Thanks for your help!
Van

 

[pbaldy]

Me.ListboxName.Column(x)

where x is the number of the desired column, starting with 0. The second column would be 1.

 

[F.I.G.]

Me.ListboxName.Column(x)

where x is the number of the desired column, starting with 0. The second column would be 1.

It is working, THANK YOU ALL, THANK YOU, THANK YOU…!
I thought Excel was cool, but Access is even better and again thank you!
Van

 

[pbaldy]

Happy to help!

 

[F.I.G.]

Happy to help!

Now, I am trying to give a default value for "RequestDate" field based on selection from the list box. I attempted with few variation, but none of them work. Please advise what did I do wrong(in blue).


Private Sub List38_DblClick(Cancel As Integer)

DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.List38.Column(1) & "#"

1st Trial
Forms!F_LoanDocuments!RequestDate = "#" & List38.Column(1) & "#"

2nd Trial
RequestDate = Forms!F_BorrowerLoanHistory!"#"& List38.Column(1) &"#"

DoCmd.GoToRecord , , acNewRec

End Sub

 

[John Big Booty]

You can use the OpenArgs property of the OpenForm method to pass information between two forms, so your first piece of code would look like;

DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.List38.Column(1) & "#", , , Me.List38.Column(1)

You can now test the OpenArgs in the On Load event of form F_LoanDocuments using the following code;

If Not IsNull(OpenArgs) Then
     DoCmd.GoToRecord , , acNewRec
     Me.RequestDate = OpenArgs
End If

 

[F.I.G.]

Its works, Thank you so much Big John for your help and reference!!!!!!!

 

[F.I.G.]

You can use the OpenArgs property of the OpenForm method to pass information between two forms, so your first piece of code would look like;

DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.List38.Column(1) & "#", , , Me.List38.Column(1)

You can now test the OpenArgs in the On Load event of form F_LoanDocuments using the following code;

If Not IsNull(OpenArgs) Then
     DoCmd.GoToRecord , , acNewRec
     Me.RequestDate = OpenArgs
End If

Private Sub ListBoxRequest_DblClick(Cancel As Integer)

DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.ListBoxRequest.Column(2) & "#", , , Me.List38.Column(2) & Me.List38.Column(3)

End Sub


Hi John,

I added a second column to your recommanded code, now how can I separate them into two different controls (column(2)=TxtBoxRequestDate and column(3)=cboTransactionType) on the F_LoanDocuments. Currently records from list box are lumped together on the F_LoanDocuments.

Thanks,
Van

 

[pbaldy]

Looks like you're trying to pass multiple items?

http://www.baldyweb.com/OpenArgs.htm

 

[F.I.G.]

Looks like you're trying to pass multiple items?

http://www.baldyweb.com/OpenArgs.htm

Thanks Paul for the reference,

What is the meanning of "Run-time error '9': Subscript out of range"?

I double checked for spelling, but keep still I am getting that error message "Run-time error '9': Subscript out of range". In my list box contain 4 columns, I need info from column 3 and 4 to be fed into the secondary form. Please note that column 3 is date field.

What did I do wrong or what am I looking for?


List Box

Private Sub List38_DblClick(Cancel As Integer)


DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.List38.Column(2) & "#", , , Me.List38.Column(2) & ";" & Me.List38.Column(3)
End Sub


F_LoanDocuments

Private Sub Form_Load()

Dim strOpenArgs() As String

If Not IsNull(Me.OpenArgs) Then

strOpenArgs = Split(Me.OpenArgs, ";")
Me.TxtBoxRequestDate = strOpenArgs(2)
Me.cboTransactionType = strOpenArgs(3)

End If

End Sub

 

[pbaldy]

However many columns are in your combo, you only passed two, so the Split only has two.

 

[F.I.G.]

However many columns are in your combo, you only passed two, so the Split only has two.

I found my error, but now I am having another issue with the second value in the strOpenArgs(1). I am keep getting an error message: "Run-time error '-2147352567 (80020009)': You can't assign a value to this object."

Is it because that I am trying to pass a alpha value (form one) into a numerical field (form two)? This problem went away when I changed the cboTransactionTypeID field into Text.

How can I change an alpha value into a numerical value?

Thank you.
Van

List Box

Private Sub List38_DblClick(Cancel As Integer)


DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.List38.Column(2) & "#", , , Me.List38.Column(2) & ";" & Me.List38.Column(3)
End Sub


F_LoanDocuments

Private Sub Form_Load()

Dim strOpenArgs() As String

If Not IsNull(Me.OpenArgs) Then

strOpenArgs = Split(Me.OpenArgs, ";")
Me.TxtBoxRequestDate = strOpenArgs(0)
Me.cboTransactionTypeID = strOpenArgs(1)

End If

End Sub

 

[pbaldy]

You could use CStr(), but the error doesn't sound like a data type mismatch. You didn't change anything else to get it to work?

 

[F.I.G.]

You could use CStr(), but the error doesn't sound like a data type mismatch. You didn't change anything else to get it to work?

Thanks Paul for your suggestion, but that would not solve my problem. Yes, I think you are right, it is a data miss match.

Little background of what I am trying to accomplish, in form one there is a list box (generated by a query) that has 4 columns, but column 1 and 2 are IDs and also hidden. In column 3 is a date field and in the 4th column is text field. When a user double clicks on any selection, it will open a second form.

I was able to successfully to pass on date value into the second form (also a date field), but hitting brick wall with 4th column of the list box.

The field for 4th column in the second form is a numerical, but convert into a combo box, selection made by user with be stored as numerical value “cboTransactionType”.

How can I pass text value into a combo box in the second form that will be stored as a numerical value?

I tried to use Dlookup, but I am getting error message like: “Run-Time error ‘3075’: Syntax error (missing operator) in query expression ‘TransactionType=New Loan’.

Table for T_TransactionType has:

TransactionTypeID TransactionType
1 New Loan
2 Renewal
3 Amendment


List Box

Private Sub List38_DblClick(Cancel As Integer)


DoCmd.OpenForm "F_LoanDocuments", , , "LoanInfoID=" & LoanInfoID & " And RequestDate = #" & Me.List38.Column(2) & "#", , , Me.List38.Column(2) & ";" & Me.List38.Column(3)
End Sub


F_LoanDocuments
Private Sub Form_Load()

Dim strOpenArgs() As String

If Not IsNull(Me.OpenArgs) Then
strOpenArgs = Split(Me.OpenArgs, ";")
Me.TxtBoxRequestDate = strOpenArgs(0)
Me.cboTransactionTypeID = DLookup("TransactionID", "T_TransactionType", "TransactionType=" & strOpenArgs(1))
End If

End Sub

 

[pbaldy]

Try

DLookup("TransactionID", "T_TransactionType", "TransactionType='" & strOpenArgs(1) & "'")

 

[F.I.G.]

Still no luck, different kind message: "Run-time error '2471': The expression you entered as a query parameter produced this error: 'TransactionID'"