From a from open a subform that opens at the same record as the original form

[Silvera]

I’ve been trying to do what I thought was a simple task. I'm tracking several documents with my data base. Each document is given a unique name and that field is the primary field. Here is the code from the main form.

Sending code from a label in main form
'<<<<XXX>>>> Row 8b
Private Sub BD_stuff_Click()
On Error GoTo Err_BD_stuff_Click
Dim Pstring As Variant
Dim stDocName As String
Dim stLinkCriteria As String
Pstring = CurrentRecord
stDocName = "RFIsBD_stuff"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_BD_stuff_Click:
Exit Sub
Err_BD_stuff_Click:
MsgBox Err.Description
Resume Exit_BD_stuff_Click
End Sub

This is the subform code
Note I have a series of buttons to navigate between records, this code establishes and sets up the buttons [first, previous, next, last, new, duplicate]

Receiving code of subform
'<<<<XXX>>>> On opening
Private Sub Form_Current()
On Error GoTo Err_Form_Current
Dim rs As DAO.Recordset
Dim Count As Integer, Position As Integer
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
Count = rs.RecordCount
Me!txtRecCnt = "of " & Count
Position = Me.CurrentRecord
Me!txtRecPos = Position

If Position = 1 Then
Me!gotoPrevious.Enabled = False
Else
Me!gotoPrevious.Enabled = True
End If

If Position > Count Then


Me!gotoNext.Enabled = False
Me!gotoNew.Enabled = False

End Sub

[DepricatedZero]

Lets clarify the terms form/subform real quick.

A Subform, as I understand, is a form -inside- of another form. There's a subform control that will let you bind the subform to the parent form.

For the OpenForm command you can (at least in 2007, should carry to 2010) include a condition statement for the form's query

For example:

If Not (lvwAssessments.SelectedItem Is Nothing) Then 
   DoCmd.OpenForm "frmImpAssDel", , , "[lngIAIDcnt]=" & Me.lvwAssessments.SelectedItem.text, acFormEdit, , "Delivered"
End If

[Silvera]

Maybe that is the problem. I call it a subform but it is really not inside of the calling form. It is supplemental/expanded infomaition to the initial form.

[Silvera]

I gave up on this last year. DepricatedZero response was Greek to me. I still need to resolve this. From a basic data entry from I want to close that form and open another form that is also a data entry form and have the second from open at the record that was previously being edited when the first form when it was closed.

[Madmax2]

Form a
form b

Primary key field on table. Pkf1


field must be on both form but can be hidden if you dont want to see it.


add a button on form a. Get rid of wizard if it pops up. Under properties, event tab click the ... On the on click event line. Click macro builder


once inside click on first line and pick open form from drop down menu. Put in form b name. Then on the where condition line put ="[pkf1]='"&[pkf1]&"'"

then next drop down pick close window and under form name put form b name......this should work if i didnt mess up. I will try to check back

[Silvera]

Thanks Madmax2, I'll give it a try this evening. The field is visible on both forms. I have a label that is use to close the existing form and open the other one. So I should replace the coding I tried previously with a new macro triggered by the label or do I have to have a button?. I looks like the similar idea I was trying with code and passing variables between the 2 forms.

[June7]

DepricatedZero's suggestion is VBA alternative to the macro construct to open form filtered to specific record. Since you posted VBA code we would assume you understand VBA programming and prefer it over macro.

[Silvera]

When I first started learning Access I used simple macros, but as I progress when I needed assistance the responses were always with code. I got use to using code but I do not have a firm understanding. That is why I wrote "DepricatedZero response was Greek to me." I know what a "If then" statement is but I did not understand his "lvwAssessments", new to me. "frmImpAssDel" I'm guessing is a form name. And , , , "[lngIAIDcnt] {also new to me}=" & Me.lvwAssessments.SelectedItem.text, acFormEdit, , "Delivered" {does this mean the form name or is "Delivered" set statement} The code I listed previously was my attempt to adapt/modify some code I have that was working. As the old saying sort of goes: I know a little which can be dangerous. I appreciate your assistance. It is great that there are folks like you, DepricatedZero's, and Madmax2 take the time to help novices like me.

[Silvera]

Madmax2, I tried to make the macro you suggested but the "Where Condition" did not run I tried several combination of " & ' but no luck. they did not seam balanced ''...'''...'''''
Attachment 17448

[June7]

Me.lvwAssessments.SelectedItem.text was offered as an example of control name to get value from the control, possibly a listbox. However, really just should be Me.YourControlNameHere. "Delivered" I think was attempt to set OpenArgs argument which is then available to the second form for various purposes. However, the value is in wrong position.

Could search Access Help or web on the OpenForm method and its arguments to learn more details.

I tried downloading your attachment but get 'invalid attachment' error.

[Silvera]

Thanks, I'll have to look into Access Help and the web on the OpenForm method. I think the {="[pkf1]='"&[pkf1]&"'"} is on the right track but the quotes are not balanced properly. I'm going to try different combination of " & ' quote marks to see if I can get the macro to work. I'm back at work this morning so maybe this after I can pursue this further. Oh the attachment was just a screen shot the macro error. I saw a post with an imbedded screenshot and tried to do the same...

[June7]

Is pkf1 a text or number field? Number fields do not require delimiters for parameters - remove the apostrophes.

[Silvera]

It is a text field because occasionaly there is a letter add "R" add to the numbers for "Revised or Revision" followed by 1 or 2 etc.

[June7]

The syntax for the filter criteria is correct except maybe need spaces before and after the &.