Losing Bound Controls (1 Viewer)

[kirkm]

Not sure if anyone can help with this. It's hard to explain and doesn't really make sense. I have an Open form with various controls bound to a query.
On the Form there's a text box that lets me change the record source, and this all works as expected.
However, if I enter rubbish into the text box (or nothing) it should do nothing

Private Sub Text309_KeyDown(KeyCode As Integer, Shift As Integer)
 If KeyCode = 13 Then
    Dim T As String
    T = Nz(Me.Text309.Text)
    If Nz(DLookup("Prefix", "tblMain", "Prefix = '" & T & "'")) > "" Then
        LocalPrefix = T
        ImportedPrefix = ""
        Form_frmShowRecord.Activate
    End If
 End If
End Sub

Activate is not Form_Activate but my own procedure, (admitedly) badly named. But anyway I would expect nothing to happen if T isn't found in Dlookup.
But what it does is null out all the bound control, leaving only unbound ones populated.

I can't see why, or how and would like to know. If I step through the above code, as soon as it Ends the bound controls go, even though no other code seems to be running. In the debug window , at that point if I print the Forms record source it is still correct.

 

[arnelgp]

better to Rename the "Activate" proc (there is already an Intrinsic Activate event on the form).
also, you need to Undo the control when there is duplicate found.

 

[kirkm]

I renamed Activate to MyActivate but that didn't alter the behaviour. But then found if Form_frmShowRecord.MyActivate was called evreytime (regardless of DLookup result) it behaved as intended. But that doesn't help understand what was wrong.
> Undo the control when there is duplicate found.
What does that mean ? What duplicate ?
Thanks

 

[arnelgp]

maybe move your code to Change Event of the control.

 

[The_Doc_Man]

what it does is null out all the bound control, leaving only unbound ones populated.

If you do something that has the effect of changing the .RecordSource of a form, it automatically does a .Requery as part of that process. IF your substitute .RecordSource has the effect of wiping out the previous .RecordSource, then the .Requery returns nulls to everything that is bound. Unbound things would not be affected. See the NOTE in the middle of the Remarks section of the below link.

Form.RecordSource property (Access)

Office VBA reference topic

docs.microsoft.com

 

[kirkm]

Thanks Doc Man, I can follow the logic of that but not why hitting Enter in a text box would change / alter the Forms record source.

 

[The_Doc_Man]

If the action behind the scenes of that special textbox causes an update of the .RecordSource, even if only to add extraneous characters to the end of the SQL, that might invalidate the .RecordSource, thus returning nulls. There is a "thing" called "SQL Injection" that is consider a type of hack and depending on your version of Access, might block that. We can guess all night, but the definitive test is if you can set breakpoints and single-step through the code behind the text box, you can SEE what actually happens.

 

[kirkm]

I've been trying that - single-step - but it steps to nowhere. After "End Sub" stepping stops and the controls lose their values.
One differnce though - if not stepping - as well as losing values another (unbound) text box turns the text black (selected).

 

[arnelgp]

show your code of MyActivate sub/func.

 

[kirkm]

Public Sub MyActivate()

    On Error GoTo err_99
    Dim temp
    DoCmd.Close acForm, "frmRecordselect"
    Me.txtImport = ""
    
    Me.txtBImport = ""
    If Form_frmDetailByArtist.Visible = True Then
        Form_frmDetailByArtist.Visible = False
        ReOpen = 1
    End If
    temp = SysCmd(acSysCmdClearStatus)

    'Clear Temp Table
    DoCmd.SetWarnings False
        DoCmd.RunSQL ("Delete * From tblExport")
    DoCmd.SetWarnings True
    Me.lbliLen.Caption = 0

    If LocalPrefix > "" Then
            PrepLocal (LocalPrefix)
        ElseIf ImportedPrefix > "" Then
            SetUpForImport (ImportedPrefix)
        Else
            MsgBox "No Valid Prefix has been passed to Me", vbExclamation
            DoCmd.Close acForm, "frmShowRecord"
            Exit Sub
    End If
    Me.btnExport.Caption = "Export " & Me.txtPrefix
    Me.lblPath.Caption = ResourcePath()
    Me.lblExcelComments.Visible = Nz(Me!Comments) Like "*[[]*]*"
    Exit Sub

err_99:
    MsgBox Err.Description
    Resume Next

 

[arnelgp]

can't tell either.
there are variables (on Form? or global?).
there are other sub/func (PrepLocal/SetUpForImport).

 

[gemma-the-husky]

@kirkm

What are you actually trying to do? Maybe we can suggest another way.

maybe this is the problem. If you enter garbage then the dlookup will fail, but you don't have a value for the nz and maybe this is giving you unexpected side effects.

If Nz(DLookup("Prefix", "tblMain", "Prefix = '" & T & "'")) > "" Then

perhaps this with the extra red characters
If Nz(DLookup("Prefix", "tblMain", "Prefix = '" & T & "'"),"") > "" Then

you could even try this, then you will be able to clearly see if the error is caused by a null result.
result = Nz(DLookup("Prefix", "tblMain", "Prefix = '" & T & "'"))
if result>"" then

 

[kirkm]

> What are you actually trying to do?
If txt309 is found in tblMain it becomes 'LocalPrefix" and MyActivate passes it to a a proc called ' PrepLocal'. This sets up a new recordsource for the form. But this is (should be?) irrelevant as the problem only happens when an empty text309. I tried a check for Result as you outlined but this didn't change anything. It will work if MyActivate is called no matter what text309 is but if "" it just sets the record source to what it already is as (global) LocalPrefix doesn't change.
What I can't figure is why hitting Enter in an empty textbox (where the KeyDown event does nothing except DLookup) changes anything.
I added this line at the start of the Keydown event

If KeyCode = 13 And Nz(Text309.Text) = "" Then Exit Sub

Then tried

If KeyCode = 13 And Nz(Text309.Text) = "" Then MsgBox LocalPrefix: Exit Sub

The Msgbox Showed the expected value but in all cases the values disappeared. And a check of the record source shows it hasn't altered.

 

[Minty]

Be aware that "" is not the same as Null, So

Nz(Text309.Text)

is not the same as

Nz(Text309.Text,"")

Also, I'm not sure that this test

If LocalPrefix > "" Then

Is reliable, I would use

If Len(LocalPrefix  & "") > 0  Then

 

[gemma-the-husky]

@kirkm

Is there any way you can post the database so that we can see it.

There are a few things that aren't clear from what you have posted - for instance
- do you have option explicit declared at the the top of all your modules
- I notice your variable temp in myactivate is not typed. It would be better to type all variables

I wouldn't be surprised if your error is caused by nulls as @Minty just noted. A null is not the same as a zero length string.

The point of adding the "result" assignment was to make it easier to see the value you retrieve
When you step through your code, you should be able to examine variables and identify any that aren't being set in the way you expect.

Clearly if it isn't doing what you expect - then either you have a logic error - so it's doing what you told it to, but your logic was wrong - or it isn't doing what you expect because your code isn't correct.

Out of interest, do you get any errors at all? - your error handler just carries on as if any error can be ignored. I expect any error will prevent your code working correctly- most of the time you just can't ignore any error. You might be able to dismiss some, but most of the time an error is an unexpected exception.

 

[kirkm]

It may be a null error but I can't nail it down positively, Much of the code I can step through and see no errors. But this particular issue won't show up with Step. I have a workaround but really curious what is wrong and why. I understand its difficult to debug without all the code and uploading the db would help. I'll try and create a cut-down version to demo just the problem. Thanks for your help. Minty I thought nz(xyz) evaluates anything into a string. What is nz(xyz, "") ?

 

[Minty]

According to Mircosoft M$ Docs ;

Nz ( variant [, valueifnull ] )

The Nz function syntax has these arguments:

Argument	Description
variant	Required. A variable of data type Variant.
valueifnull	Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. Note: If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,

If the value of variant isn't Null, then the Nz function returns the value of variant.

So in theory you do get an empty string "" as a default, but I would always want to force a specific result as reading the details it can return a null in certain circumstances.

I think because you can't omit the replacement value in a query it is just good practice to always supply it.

 

[arnelgp]

just edit post #14, because readers may construed as correct.

debug.print Len(Nz(Null))
result: 0

 

[Minty]

just edit post #14, because readers may construed as correct.

debug.print Len(Nz(Null))
result: 0

@arnelgp I deliberately didn't use Nz - so I don't know why you have?

Sub Test1()
    
    Dim vVAr As Variant
    
    Debug.Print Len(vVAr & "")
 
End Sub
Immediate window:

test1
0

 

[arnelgp]

@arnelgp I deliberately didn't use Nz - so I don't know why you have?

so on post #14, you said:

Nz(Text309.Text) is not the same as Nz(Text309.Text, "")?