Command Button filter on different form

[kpfunf]

I have one form called Accounts, on which there is a command button called EnterNewTransactionButton. This opens another form called Transactions. I want to add some code that will take the value of the AccountNameBox on the form Accounts, and filter the Transactions form with this value. I'm having trouble trying out the various filter form code I've read.

Additionally, I'd love to add a fancy feature: once on the Transaction form and filtered, I'd like to start the new record with the account name that is filtered (not a big deal, but a nice touch :).

I attached a sample database below.

[rsmccli]

This will work for the filtering as the forms stand now. I would put it in the Form_Load event of the subform. You may also want to add some code to check if the Accounts form is open, especially if it will be opened from anywhere besides the Accounts form.

If Len(Forms.Accounts.AccountNameBox) <> 0 Then
  Me.FilterOn = True
  Me.Filter = "Lookup_VendorID.AccountName='" & Forms.Accounts.AccountNameBox & "'"
Else
  Me.FilterOn = False
  Me.Filter = ""
End If

As a side note, is there a reason why the two corresponding comboboxes are not both 2 column lookups? One is a one column "text" combobox and the other is a standard 2 column with a PK lookup. Just wondering. It would probably help to synchronize those if you want the VendorID combo in the subform to autofill with the selection from AccountNameBox.

hth
rsmccli

[kpfunf]

rsmccli,
Thanks for the code, works great.

Which boxes were you referencing?

Also, do you know how to start a new record with the vendor selected?

[rsmccli]

AccountNameBox and VendorID are the two combos.

btw you are definitely going to want to add a check to see if the account form is open because I see you can access your transactions form from your switchboard. Here is one that I have used that was present in an existing database I work with. It is not pretty, but it works.

Function IsLoaded(MyFormName)
' Returns True if specified the form is loaded;
' False if the specified form is not loaded.
On Error Resume Next
    Dim i
 
    IsLoaded = False
    For i = 0 To Forms.Count - 1
      If Forms(i).FormName = MyFormName Then
        IsLoaded = True
        Exit Function       ' Quit function once form has been found.
      End If
    Next
 
End Function

So once that is pasted into a module you could replace the first line of code from earlier with:

If IsLoaded("Accounts") = True And Len(Forms.Accounts.AccountNameBox) <> 0 Then

If you do sychronize your combos, once you are on your new record I think you could just put:

Me.VendorID = Forms.Accounts.AccountNameBox

hth

[kpfunf]

rsmccli,

Thanks again. I changed the combos like you suggested.

The last line of code didn't work though. I've never used code to write a record, so not really sure how to. Any other ideas?

[rsmccli]

I just removed the now redundant DoCmd.GoToRecord , , acNewRec from the command button code and put it in the Transactions subform on load event. Works for me. Sorry I didn't realize you already had a function in place to check for forms being open.

Private Sub Form_Load()
 
If IsLoaded("Accounts") Then
  If Len(Forms.Accounts.AccountNameBox) <> 0 Then
    Me.Filter = "Lookup_VendorID.VendorID=" & Forms.Accounts.AccountNameBox
    Me.FilterOn = True
    DoCmd.GoToRecord , , acNewRec
    Me.VendorID = Forms.Accounts.AccountNameBox
  Else
    Me.Filter = ""
    Me.FilterOn = False
    DoCmd.GoToRecord , , acNewRec
  End If
End If
 
End Sub

[NeoPa]

...
The last line of code didn't work though. I've never used code to write a record, so not really sure how to. Any other ideas?

Try :

Me.VendorID = Forms!Accounts.AccountNameBox

I suspect it was simply a typo as Forms.Accounts... doesn't resolve.