Change Forms Record Source from another Form

[jw5107]

There has got to be a better/more effiecent way of doing this....
On a form (frm1) - I have a command button. With this button I would like to be able to open another form (frm2). This form needs to "share record sources". The record sources are tables.
I'm tryin' to use 1 form instead of haveing multiple forms for each record source. I have searched, and can't seem to find something I can use for my application...
Below is the best my "2nd grade Access Education" can come up with... There has got to be a better way..!!
Can somebody break it down for me..!!!

Private Sub cmdbtn2_Click()
DoCmd.OpenForm "MultiWWReviewMap", acDesign, , , acFormPropertySettings, acHidden
Forms!MultiWWReviewMap.RecordSource = "MultiReviewMap"
DoCmd.OpenForm "MultiWWReviewMap", acNormal, , , , acWindowNormal
end sub

Like I said this way is working, and each time frm2 is closed it prompts for a save... because the record source has changed.... I would like to keep the record source "" on close...

Thanks in advance..!!
jw5107

 

[RoyVidar]

I think that if you change recordsource after the form is opened, then you shouldn't get the save prompt.

I e, in stead of opening in design view, then toggle, just

[tt]DoCmd.OpenForm "MultiWWReviewMap", acNormal
forms!MultiWWReviewMap.recordsource = vbnullstring ' for safety?
forms!MultiWWReviewMap.recordsource = "MultiReviewMap"[/tt]

Roy-Vidar

 

[danvlas]

In MultiWWReviewMap, Open event:

Private Sub Form_Open(Cancel as Integer)
Dim frm As Form
On Error Resume Next

Set frm=Screen.ActiveForm
If Err.Number<>0 Then
Me.RecordSource = frm.RecordSource
End If

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jw5107]

RoyVidar & danvlas,

Alright - now we are getting somewhere...!! Thanks for the fast responses...
Here is where the fun starts...
RoyVidar -
I think I do need for the record source to be changed BEFORE the form opens... On frm2, I have code for the "On Currrent" event. Which is shown below... The code opens a recordset based on the IID field/txt box on frm2. So I'm thinking that I'm gettin' the error message due to the record source of frm2 not being change until after it opens..?? Any fixes...?? Hope this makes sense...

danvlas-
I'm not sure what your example is doing...?? Can you elaborate..??
Thanks for the help from both of ya..!!!
jw5107

***frm2 - On Current Event***
DoCmd.Hourglass (True)
Dim db As DAO.Database
Dim rstAlloc As DAO.Recordset
Dim ctrl As Control
Dim varMultiUsage As Variant
Dim i As Integer
Set db = CurrentDb
Set rstAlloc = db.OpenRecordset("SELECT IID, Allocation, Allocated, OverAllocated, OnBackOrder, PooledGTWYs FROM AllocationStatusMain WHERE IID = '" & Me!IID & "'", DB_OPEN_DYNASET)

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
Select Case ctrl.Tag
Case Is = "Clearme"
ctrl.ForeColor = vbBlack
ctrl.BackStyle = 0
ctrl.BorderStyle = 0
ctrl.BackColor = 16777215
ctrl.FontBold = False
End Select
End If
Next ctrl
Me!lblNoStock.Visible = False

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
Select Case ctrl.Tag
Case Is = "Clearme"
'ctrl.Value = Null
End Select
End If
Next ctrl

If rstAlloc.RecordCount = 0 Then
Me!lblNoStock.Visible = True
DoCmd.Hourglass False
Me!ParkCsr.SetFocus
Exit Sub
Else
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
Select Case ctrl.Caption
Case Is = !Allocated
ctrl.BackStyle = 1
ctrl.ForeColor = 16711680
ctrl.FontBold = True
Case Is = !OverAllocated
ctrl.BackStyle = 1
ctrl.BackColor = 12058623
ctrl.FontBold = True
Case Is = !OnBackOrder
ctrl.BackStyle = 1
ctrl.BackColor = 8434687
ctrl.FontBold = True
Case Is = !PooledGTWYs
ctrl.BackStyle = 1
ctrl.BackColor = 12582847
ctrl.FontBold = True
End Select
.MoveNext
Next i
End With
End If
Next
End If

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
Select Case ctrl.Name
Case Is = !Allocated
ctrl.Value = !Allocation
ctrl.BackStyle = 1
Case Is = !OverAllocated
ctrl.Value = !Allocation
ctrl.BackStyle = 1
Case Is = !OnBackOrder
ctrl.Value = !Allocation
ctrl.BackStyle = 1
End Select
.MoveNext
Next i
End With
End If
Next

varMultiUsage = DLookup("GTWYsUsage", "qryMultiIIDGTWYUsage", "!IID=[IID]")
If (Not IsNull(varMultiUsage)) Then Me![USAGE] = varMultiUsage

DoCmd.Hourglass (False)
Me.Refresh

 

[danvlas]

It's somehow another flavour of Roy's suggestion, but it takes the form name from the system. If you ever change the name of form 1, the code will still work.

Open MultiWWReviewMap form in Design View. Click View-Code. Slect (from left combo) Form, select (from right combo) Open.
Type:

Dim frm As Form 'object variable to point to a form
On Error Resume Next 'do not stop the code in case of error

Set frm=Screen.ActiveForm 'set frm variable to the form on screen
If Err.Number = 0 Then 'there is a form displayed on screen
Me.RecordSource = frm.RecordSource ' setthe rec source
End If


Looking back, I can see I made a stupid mistake: Err.Number =0 is correct.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

How are ya jw5107 . . .

[blue]Like I said this way is working, and [purple]each time frm2 is closed it prompts for a save...[/purple] because the record source has changed.... [purple]I would like to keep the record source "" on close..[/purple][/blue]

You need a [blue]non-volatile[/blue] source to hold your recordsource. A [blue]custom DB property[/blue] would fill the bill here. Each time you change the recordsource you would save it as well in the custom property (holding the last). In the Open event of the form you would set the recordsource via the custom property. Once the form is open (formview) you'll be able to change the recordsource on the fly! . . . and hold the last.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .