Changing Record Source (1 Viewer)

N

NearImpossible

Registered User.
Local time
Today, 08:06
Joined
Jul 12, 2019
Messages
225
Wondering if someone can point out the obvious as I seem to be missing it more and more lately.

I would like to use the same form for entering notes, however, depending on underlying form that it is launched from, the Record Source could be different.

I am using a double click event to launch the form with the following code
Code: 
Private Sub Equipment_DblClick(Cancel As Integer)

    DoCmd.OpenForm "FacilityEquipmentNotes", acDesign, WindowMode:=acHidden
        Forms!FacilityEquipmentNotes.SourceObject = "dbo_FacilityEquipmentNotes"
    DoCmd.Close acForm, "FacilityEquipmentNotes", acSaveYes
            
    DoCmd.OpenForm "FacilityEquipmentNotes", , , "[EquipmentID]= " & Me.[EquipmentID]
    
End Sub

However I am getting Run-Time error 2465: Application-defined or object-defined error and it highlights on the following
Code: 
Forms!FacilityEquipmentNotes.SourceObject = "dbo_FacilityEquipmentNotes"

If I mouse over dbo_FacilityEquipmentNotes it shows dbo_FacilityEquipmentNotes=empty, which is not the case and if I just specify dbo_FacilityEquipmentNotes as the source on the form properties, it works just fine
 
Last edited:
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:06
Joined
Oct 29, 2018
Messages
21,467
Hi. SourceObject is mainly for subforms. I might just use an unbound form and simply pass the Note back to the calling form.
 
June7

June7

AWF VIP
Local time
Today, 05:06
Joined
Mar 9, 2014
Messages
5,470
Your narrative says you want to change RecordSource yet your code changes SourceObject. SourceObject is a subform container control property.
 
M

Micron

AWF VIP
Local time
Today, 09:06
Joined
Oct 20, 2018
Messages
3,478
Notwithstanding the confusion about RecordSource vs SourceObject:
I would like to use the same form for entering notes, however, depending on underlying form that it is launched from, the Record Source could be different.
Click to expand...
I take that to mean the same form for notes that are related to various 'things' so the data source for the notes form is different sometimes. Regardless, don't repeatedly modify a form in design view to do whatever it is you're doing. If all you want to do is alter the form record source, do so in the open event of a form (or SourceObject, as the case may be).
If I mouse over dbo_FacilityEquipmentNotes it shows dbo_FacilityEquipmentNotes=empty
Click to expand...
Because that would be Me.dbo_FacilityEquipmentNotes. You are saying it is equal to the string "dbo_FacilityEquipmentNotes" which it is not.
 
N

NearImpossible

Registered User.
Local time
Today, 08:06
Joined
Jul 12, 2019
Messages
225
June7 said:
Your narrative says you want to change RecordSource yet your code changes SourceObject. SourceObject is a subform container control property.
Click to expand...

yes, I wanted to change the Source Object so please forgive my wording as obviously RecordSource refers to something completely different, but as previously Identified by DBguy, Source Object is for SubForms so I am going back to the drawing board.

Appreciate everyone's input
 
M

Micron

AWF VIP
Local time
Today, 09:06
Joined
Oct 20, 2018
Messages
3,478
Post back if you get stuck; start a new thread if it's not related to this problem.
Maybe all you need is to effect which recordsource you use for the one and only notes form. Either the combo after update event or something that is driven by user action (such as a button click) would be fine. As was pointed out, you can also use an unbound form for notes, but that might be a bit more complicated than what you're up for at present.
Good luck!
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:06
Joined
Oct 29, 2018
Messages
21,467
The unbound Notes popup form I was thinking about was nothing more than a glorified Zoom Box (like a popup calendar, if you will).
 
M

Micron

AWF VIP
Local time
Today, 09:06
Joined
Oct 20, 2018
Messages
3,478
theDBguy said:
The unbound Notes popup form I was thinking about was nothing more than a glorified Zoom Box (like a popup calendar, if you will).
Click to expand...
I figured as much. Not sure how popular the idea is but I've seen it mentioned (probably here) before.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:06
Joined
Oct 29, 2018
Messages
21,467
Micron said:
I figured as much. Not sure how popular the idea is but I've seen it mentioned (probably here) before.
Click to expand...
And since it won't have anything but one big Textbox control, it shouldn't be too complicated to code either.
 
Cronk

Cronk

Registered User.
Local time
Today, 23:06
Joined
Jul 4, 2013
Messages
2,772
Use the recordsource property and OpenArgs
eg
docmd.openform "FacilityEquipmentNotes",,,,,,"dbo_FacilityEquipmentNotes"

In the form's open event
me.recordsource = "select * from " & me.openargs
 
N

NearImpossible

Registered User.
Local time
Today, 08:06
Joined
Jul 12, 2019
Messages
225
After looking at this a little closer, I guess I was looking more for pre-populated field sources from the underlying form vs a different source object.

I was able to make it work using unbound text boxes and the OpenArgs based on where it is opened from. I also have 2 other bound input fields where notes will be entered as the fields identified below are more for referencing the selected device.

Code: 
If Me.OpenArgs = "EquipmentNotes" Then
    Me.EquipmentID = [Forms]![Facilities]![FacilityEquipment subform].[Form]![EquipmentID]
    Me.NotesName = [Forms]![Facilities]![FacilityEquipment subform].[Form]![EquipmentName]
    Me.SerialNumber = [Forms]![Facilities]![FacilityEquipment subform].[Form]![Serial Number]
    Me.AssetTag = [Forms]![Facilities]![FacilityEquipment subform].[Form]![Asset Tag]
    Me.Location = [Forms]![Facilities]![FacilityEquipment subform].[Form]![Location]
    
ElseIf Me.OpenArgs = "TicketAdd" Then
    Me.EquipmentID = [Forms]![AddTicketEquipment]![EquipmentID]
    Me.NotesName = [Forms]![AddTicketEquipment]![EquipmentName]
    Me.SerialNumber = [Forms]![AddTicketEquipment]![Serial Number]
    Me.AssetTag = [Forms]![AddTicketEquipment]![Asset Tag]
    Me.Location = [Forms]![AddTicketEquipment]![Location]
    
ElseIf Me.OpenArgs = "EquipmentSearch" Then
    Me.EquipmentID = [Forms]![Equipment Search]![EquipmentID]
    Me.NotesName = [Forms]![Equipment Search]![EquipmentName]
    Me.SerialNumber = [Forms]![Equipment Search]![Serial Number]
    Me.AssetTag = [Forms]![Equipment Search]![Asset Tag]
    Me.Location = [Forms]![Equipment Search]![Location]
    
ElseIf Me.OpenArgs = "Transfer" Then
    Me.EquipmentID = [Forms]![TransferFacilitySelector]![TransferFacilityEquipment Subform].[Form]![EquipmentID]
    Me.NotesName = [Forms]![TransferFacilitySelector]![TransferFacilityEquipment Subform].[Form]![EquipmentName]
    Me.SerialNumber = [Forms]![TransferFacilitySelector]![TransferFacilityEquipment Subform].[Form]![Serial Number]
    Me.AssetTag = [Forms]![TransferFacilitySelector]![TransferFacilityEquipment Subform].[Form]![Asset Tag]
    Me.Location = [Forms]![TransferFacilitySelector]![TransferFacilityEquipment Subform].[Form]![Location]

End If

Good to know that the source object is used for SubForms though as I do have a couple situations where that will come in handy.
 
Last edited:
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 06:06
Joined
Oct 29, 2018
Messages
21,467
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
M

Micron

AWF VIP
Local time
Today, 09:06
Joined
Oct 20, 2018
Messages
3,478
FWIW, when I see a lot of repeated code I feel compelled to promote the use of variables or aliases. So you can also do like:
Code: 
Dim frm As Form

Select Case Me.OpenArgs
 Case "EquipmentNotes"
   Set frm = [Forms]![Facilities]![FacilityEquipment subform].[Form]
   Me.EquipmentID = frm.EquipmentID
   Me.NotesName = frm.[EquipmentName]
   Me.SerialNumber = frm.[Serial Number]

 Case "TicketAdd"
   Set frm = [Forms]![AddTicketEquipment]
   Me.EquipmentID = frm.EquipmentID
and so on

End Select
One could proffer that the repeated use of Me is also a candidate for compression, but that is only 2 characters, plus you'd need to add the start and end of a With block (2 more lines per Case block) so why bother? Hopefully you can see how much easier it is to read (and less to write) just by looking at the first Case block.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 14:06
Joined
Sep 21, 2011
Messages
14,262
I'd just have the frm object set in the Case statement and then the controls set outside the Case statement?

Code: 
End Case

    Me.EquipmentID = frm.[EquipmentID]
    Me.NotesName = frm.[EquipmentName]
    Me.SerialNumber = frm.[Serial Number]
    Me.AssetTag = frm.[Asset Tag]
    Me.Location = frm.[Location]
 
M

Micron

AWF VIP
Local time
Today, 09:06
Joined
Oct 20, 2018
Messages
3,478
I'd just have the frm object set in the Case statement and then the controls set outside the Case statement?
Click to expand...
Excellent! I never bothered to notice they were the same all the way through. I saw the repetition and basically stopped reading. Maybe I should read the whole post every time?
 
You must log in or register to reply here.

Similar threads

C
Solved Treeview requery
Replies
17
Views
528
ClaraBarton
C
C
Sub to update control source of multiple reports and fields within
Replies
3
Views
207
theDBguy
theDBguy
C
Close form; open a new form
Replies
11
Views
492
isladogs
isladogs
C
Error: Cannot open any more databases
Replies
5
Views
334
cricketbird
C
C
Solved Error 3467 object is closed
2
Replies
25
Views
1,061
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom