Update Subform to unbound Textbox value change - best option

Rx_

Rx_

Nothing In Moderation
Local time
Today, 08:15
Joined
Oct 22, 2009
Messages
2,803
Question: As the unbound parent list box record selection is changed, I want the sub form to refresh.
Do I put code in the Subform Current Record event?

Master Form Name: frmsr_NewWellEntry
Unbound Listbox - when record selected the primary key populates txtNavWellID (unbound) on parent form

subform Container fsubsrNavSHLBHL Link Master Fields =Forms![frmsr_NewWellEntry].[txtNavWellID]

The read-only form in the subform Record Source is something like: select * from vsrNavigatorSHLBHL where Well_ID =90243

What would my best options be?

Key The Form used as the subform above will be re-used in multiple parent forms.
The parent form data is form SQL Server, the subform from Oracle.
 
if you are modifying the subform recordsource then there is no benefit in populating the subform control linkmaster/child fields (or txtNavWellID for than matter unless you want to see it)

put in the unbound listbox afterupdate event

fsubsrNavSHLBHL.form.recordsource="select * from vsrNavigatorSHLBHL where Well_ID =" & nameofunboundlistbox

or if your subform has a recordsource of 'select * from vsrNavigatorSHLBHL' then you would set the subform linkchild property to Well_ID and the linkmaster to the name of your unbound listbox control (do this manually, the wizard only looks at bound controls) - then there is no need for any code

The first suggestion is usually more efficient when bringing data across the network, but try both methods to see.
 
One more question - if the subform record is null - the sub form stays blank. This has probably been asked before. Any quick links or suggestions?

Thanks, got called to a meeting right at post time. Works Great!
If I use Debug.Print Forms![frmsr_NewWellEntry].[txtNavWellID] in the subform Open event - I get the wellID. The subform Current with the same code doesn't update from the parent's list box.

Thanks for pointing out to update the in the listbox after update event.
It is something handed to me, maintenance mode so the wizard is something I had hoped to avoid.

Personally, I prefer code. It is easier to locate in the code window than property settings.
What a marvelous day! Perfect solution for me! The change forces the subform to update.
Code: 
Private Sub lstNewNavWells_AfterUpdate()
    On Error GoTo ErrorHandler
    Dim strLogMessage As String    
    txtNavWellID.Value = lstNewNavWells.Value ' used for local form       
    txtWellBaseName.Value = StrConv(lstNewNavWells.Column(1), vbProperCase) ' used to display well name from lest box
    
    '*** Load up other info from Oracle link into Subform
    fsubsrNavSHLBHL.Form.RecordSource = "select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
                ' example of Navigator SHLBHL subform recordsource   select * from vsrNavigatorSHLBHL where Well_ID =90243
ProcedureExit:
    On Error Resume Next    
    ' cleanup management code here 
    Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & vbNewLine & Err.Description
    strLogMessage = "Error #" & Err.Number & " - " & Err.Description
    LogUsage "frmNewWellEntry", "lstNewNavWells_AfterUpdate", strLogMessage ' custom function to log code in error table
     Resume ProcedureExit
    Resume ' debug in developer mode
End Sub
 
Last edited:
no need to use the .value - it is the default property for the control
One more question - if the subform record is null - the sub form stays blank. This has probably been asked before. Any quick links or suggestions?
Click to expand...
the question has probably been asked before but what I suggest is if your subform is not a datasheet I would put an unbound textbox in the in the main form with a control source of ="No Data to Report" or similar and then either use your code to check the subform recordcount and make this visible if recordcount is 0, or use conditional formatting. You could move this control to your subform header or footer if your subform is not a datasheet although I'm not sure whether an event is triggered on no data.

Variations on this would be - again on checking the recordcount - is to place the unbound textbox behind the subform control then hide the subform control if recordcount is 0 (thereby exposing the textbox)
 
I just posted a seperate question as you posted. You brought up good points.
I avoid using data sheets. In the regulatory industry, each text box can require translation. That point should have been added in my description.

The recordcount at 0 is another important part of my question.
I will continue this on my next question - this is likely to generat a life of its own.
 
You must log in or register to reply here.

Similar threads

P
DSum isn't updating from subform
Replies
3
Views
426
Pat Hartman
P
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
799
CJ_London
C
N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
677
Pat Hartman
P
C
Solved No Current Record on Access Close
Replies
11
Views
768
Pat Hartman
P
R
Synching subforms after query
Replies
19
Views
603
Pat Hartman
P

Users who are viewing this thread

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