Form to open to specific record (DB attached) Question

[d9pierce1]

Views: 6 Size: 97.3 KB">Database4.zip


HI all,
I am trying to get this Edit button on primary form (Sample DB) to open another form, to specific records in the forms sub forms.
I have done this with a different db but cant seem to get it to work with this one?
I have tried the following code and neither one works correctly. It opens forms but not to specific records.

DB Opens to Primary Form
If you open it, you will see what I mean

Private Sub cmdEdit_Click()
    If Me.cboAccountTypeID = 0 Then
    
        MsgBox "You must select a Record to edit!"
  Else
    DoCmd.OpenForm "frmSystemCategoryData", , , "Forms!frmSystemCategoryData!sfrmSystemCategoryType.Form!SystemCategoryTypeID = Me.cboAccountTypeID.Column(1)" _
    & "Forms!frmSystemCategoryData!sfrmSystemCategory.Form![SystemCategoryID] = Me.cboAccountNameID.Column(1)"
    Forms!frmSystemCategoryData!sfrmSystemCategorySub.SetFocus
    DoCmd.GoToRecord , , acNewRec


  End If
End Sub


Private Sub CmdEdit_Click()
    If Me.cboAccountTypeID.Column(0) & "" = "" Then
    
        MsgBox "MsgBox "You must select a Record to edit!"
  Else
    DoCmd.OpenForm "frmSystemCategoryData"
    Forms!frmSystemCategoryData!sfrmSystemCategoryType.SetFocus
    DoCmd.FindRecord Me.cboAccountTypeID.Column(1)
    Forms!frmSystemCategoryData!sfrmSystemCategory.SetFocus
    DoCmd.FindRecord Me.cboAccountNameID.Column(1)
    Forms!frmSystemCategoryData!sfrmSystemCategorySub.SetFocus
    DoCmd.GoToRecord , , acNewRec


  End If
End Sub

Can someone assist me with this?
Thanks
Dave

[Welshgasman]

On my tablet, so dB no good ATM.
I would either use the where clause of the open form, or pass in the key as openargs and then findfirst.

[Micron]

If you don't concatenate references or variables but put them within all the quotes instead, your are not passing the reference value, you are passing a literal string.
So you are not saying WHERE somthing equals what's IN Me.cboAccountTypeID.Column(1) you are passing literally WHERE something = "Me.cboAccountTypeID.Column(1)"

[June7]

Can't use WHERE argument of OpenForm to filter a subform. OpenForm can only refer to main form, not subforms. So this would never work anywhere even if syntax were correct.

A reference that uses full path, not Me alias, can be embedded between quotes. "something = Forms!frmSystemCategoryData.cboAccountNameID". I have always concatenated with Me.

Two of the subforms have a filtered RecordSource: SELECT * FROM tblSystemCategorySub WHERE [SystemCategoryID]=1;
Then you try to filter that with VBA FindRecord for SystemCategoryTypeID. None of the records have the SystemCategoryTypeID of 5. The combobox is set to show only this one SystemCategoryTypeID.

Also, seems code behind subforms may be conflicting with the frmCOA code.

[Micron]

I didn't mean it that way. I meant the filtering was where something equals something, not to use a sql where clause. It was meant to be the WHERECONDTION of the OpenForm method. I guess I shouldn't have capitalized it? Also, this says otherwise re concatenation, so that's what I've always done

https://learn.microsoft.com/en-us/of...docmd.openform

See last example

[June7]

Yes, I know it was meant to be WHERE CONDITION argument of OpenForm. WHERE CONDITION follows SQL WHERE clause requirements. This works:

DoCmd.OpenForm "sfrmSystemCategory", , , "SystemCategoryID=Forms!frmCOA.cboAccountTypeI D"

However, including .Column(1) fails, as it would in a query object.

[Micron]

.Column(1) fails, as it would in a query object.

It works in a query if you use Eval()

[June7]

Tried. Just throws Undefined Function error.

[Micron]

Saw it posted somewhere; perhaps by Albert D. Kallal. Would have to look to see if I saved my test query, or you could Google how to use .Column property in a query field because that's how I found it.
PM to remind me if I forget to follow up and you want to see the sql. No guarantee that I saved it.

EDIT - BTW, it was a listbox or combo box column if that matters.
EDIT2, as you've probably noted before, best to post what you tried. IIRC your expression has to be quoted inside the Eval. Did you do that?

[Micron]

Couldn't find; had to re-create. This works (these are not my field/object names)

SELECT [Attendance Log].EmployeeID, [Attendance Log].[Last Name], [Attendance Log].[First Name]
FROM [Attendance Log]
WHERE ((([Attendance Log].[First Name])=Eval("Forms![Attendance Log1].Combo20.Column(2)")));

Hopefully it applies to what you were saying re: not being able to use .Column property in a query. I imagine it could work for a where condition as well, but I've never tried.

[June7]

Ok, works. Wasn't using quote marks. Doh!
Works with OpenForm also: "[First Name]=Eval('Forms![Attendance Log1].Combo20.Column(2)'")

Works with listbox as well.

[d9pierce1]

Hi June7
I have tried the following two, both open form but do not go to the record that matches my combo? I am sure it has something to do with my code but not sure?

DoCmd.OpenForm "frmSystemCategoryData", , , ("SELECT [tblSystemCategoryType].[SystemCategoryTypeID]
 FROM [tblSystemCategoryType] 
WHERE ((([tblSystemCategoryType].[SystemCategoryTypeID])=Eval('Forms![tblCOA].[cboAccountTypeID.Column(1)')))")

DoCmd.OpenForm "frmSystemCategoryData" ,,,"[tblSystemCategoryType].[SystemCategoryTypeID])=Eval('Forms![tblCOA].[cboAccountTypeID.Column(1)'")

Both Compile but only open form to first record on the first sub form
Thanks
Dave

[Micron]

Yes, the single or double quotes are required because Eval returns a string.

[Micron]

ID fields are usually column 0. Are you sure you're using the correct column (because the columns collection is zero based)?

[d9pierce1]

Hi Micron
I beleive I am,
Here is row source

SELECT [SystemCategoryID], [SystemCategoryTypeID], [SystemCategoryName] FROM tblSystemCategory WHERE [SystemCategoryTypeID]=5 ORDER BY [SystemCategoryName];