sub form

[directorz]

I've got a mainform with a pop up subform. While the focus of the mainform is on a record and I bring up the subform, I need to have the subform show the current record that the mainform is focused on. If I change the focus to a different record on the mainform, I need the subform to change as well. I know this is simple but can't see it.

Thank you
Directorz

 

[willir]

directorz

mainform with a pop up subform

You can use the Requery and / or Refresh method.

With an embedded subform, if using an SQL statement instead of the Master / Child field properties to maintain the linkage, the syntax would be ...

'If you change the record source for the form...    
'Me.YourSubform.Form.RecordSource = strSQL1
Me.YourSubform.Requery

Since your subform is a popup, you would probably have to reference the formal name of the form.

Richard

 

[directorz]

Willir,
This is going to be a popup form, not a subform...sorry

 

[directorz]

This goes back to the memo field question. I'm trying to find some options. Maybe I'm not doing this correctly. Here's some background.

TblVehicles
ID - PK
Department
Year

TblSchedule
UnitID
Checklist ( memo field)

I have a form based on TblVehicles. I want to create a popup form based on TblSchedule.Checklist. So, I can create the form & via command button get it to pop up on the mainform. How do I code the pop up so that UnitID on the pop up changes as the ID of the mainform changes and keep eveything coordinated. It's tough to word into a visualization.

 

[TheAceMan1]

How are ya directorz . . . . .

Without a relationship between the two the tables, whats the point?

See Ya! . . . . . .

 

[directorz]

TheAceMan1,

There is a relationship between ID and unitID. I used

If IsOpen("mainform") Then
Forms!frmpopup.Filter = "ID = " & _
Nz(ID, 0)
End If

Now I need to get a handle on this memo field issue

 

[TheAceMan1]

OK directorz . . . . .

The following is on the premise of your following quote:

[blue]There is a relationship between ID and unitID.[/blue]

You need to synchronize the PopUp. Note: [blue]willir[/blue] has already hinted at this. Also, from what you show in your last post, I'm taking it [blue]ID[/blue] and [blue]UnitID[/blue] are numeric. Also, you substitute any values in [purple]purple[/purple]!

[purple]Don't forget to backup the database before making any changes, so you can come back to square one if necessary.[/purple]

1) In a module in the modules window. copy/paste the following function:

[blue]Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

2) Make a query for the PopUp, returning the fields you desire. Add the following in the criteria for UnitID:

[blue]Forms![purple][b]YourMainFormName[/b][/purple]!ID[/blue]

3) Make the PopUp, using the [blue]queryname[/blue] for the [blue]RecordSource[/blue]. Do not make the popUp [blue]Modal[/blue], or you won't be able to change records on the MainForm. Bear in mind when your finished with the PopUp, the MainForm has to be open for it to work.

4) Open the MainForm in design view. Put the following code in the [blue]On Click[/blue] event of the [blue]Command Button[/blue]:

[blue]   DoCmd.OpenForm "[purple][b]YourPopUpFormName[/b][/purple]"[/blue]

5) Now, in the [blue]On Current[/blue] event of the Main Form, copy/paste the following code:

[blue]   Dim frm As String
   
   frm = "[purple][b]YourPopUpFormName[/b][/purple]"
   
   If isopenfrm(frm) And Not Me.NewRecord Then
      Forms(frm).Requery
   End If[/blue]

Thats it, give it a whirl and let me know . . . . .

See Ya! . . . . . .

 

[directorz]

Have not forgotten, I'm out for a few days on vacation