Hiding Combobox or txt box depending on if data is null (1 Viewer)

[why]

I am trying to display a txtbox if data is null and Display the combobox with the data if it has data. I am getting an run-time error '3021' No current record on
If rs!Closed_Numbers Like "" Then

Thanks for all the help.

Here is my code

Private Sub cmbMatter_AfterUpdate()
Dim strSQL As String
Dim db As DAO.Database
Dim rs  As DAO.Recordset



    Me.txtMatterName = Me.cmbMatter.Column(1)
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT FileLocationAudit.Closed_Numbers, FileLocationAudit.Client, FileLocationAudit.Matter FROM FileLocationAudit WHERE FileLocationAudit.Client = 'Me.cmbClient' and FileLocationAudit.Matter = 'Me.cmbMatter' ;")

   Me.cmbClosed.RowSource = "SELECT FileLocationAudit.Closed_Numbers, FileLocationAudit.Client, FileLocationAudit.Matter FROM FileLocationAudit " & _
    " WHERE (((FileLocationAudit.Client)=[Forms]![FixLocation]![cmbClient]) AND (FileLocationAudit.Matter)=[Forms]![FixLocation]![cmbMatter]);"

    If rs!Closed_Numbers Like "" Then
    Me.Clstxt.Visible = True
    Me.cmbClosed.Visible = False
    
    Else
     Me.cmbClosed.Visible = True
     Me.Clstxt.Visible = False
     End If
    Me.cmbClosed = ""
    Me.Combo193 = ""
 

End Sub

 

[Gasman]

Check whether any record even exists in recordset rs

Check for EOF or BOF or both.

Judging by your Select statement there will be none.?

You need to concatenate the data with the text

"SELECT FileLocationAudit.Closed_Numbers, FileLocationAudit.Client, FileLocationAudit.Matter FROM FileLocationAudit WHERE FileLocationAudit.Client = '" & Me.cmbClient & "' and FileLocationAudit.Matter = '" & Me.cmbMatter & "' ;")

This assumes both are strings as you were using single quotes to start with.

HTH

 

[why]

Check whether any record even exists is recordset rs

Check for EOF or BOF or both.

Judging by your Select statement there will be none.?

You need to concatenate the data with the text

"SELECT FileLocationAudit.Closed_Numbers, FileLocationAudit.Client, FileLocationAudit.Matter FROM FileLocationAudit WHERE FileLocationAudit.Client = '" & Me.cmbClient & "' and FileLocationAudit.Matter = '" & Me.cmbMatter & "' ;")

This assumes both are strings as you were using single quotes to start with.

HTH

Thanks for your help. This fixed the syntax errors. Your right I need to check if the record exists first and that is what I was trying to do with this. What would be a method to check if the closed number exists for that client matter? If I put a client matter that does not have a closed record instead of making the txtbox visible. I get the ' No current record error. With your corrections, if the client matter do exists the else statement works.

 

[MajP]

Is the Closed a numeric or string. If numeric not need to check for "" but maybe O. I will assume closed is a string so check for both empty and null. Also I like to use a variable in this case to make things easier to read.

dim HasValue as boolean
    HasValue = (rs!closed & "") <> ""   'This will check both null and empty
    Me.Clstxt.Visible = not HasValue
    Me.cmbClosed.Visible = hasValue

This way is a switch and you will not need the if then. Null & "" = "". So it is an easy way to check both cases.

 

[why]

Is the Closed a numeric or string. If numeric not need to check for "" but maybe O. I will assume closed is a string so check for both empty and null. Also I like to use a variable in this case to make things easier to read.

dim HasValue as boolean
    HasValue = (rs!closed & "") <> ""   'This will check both null and empty
    Me.Clstxt.Visible = not HasValue
    Me.cmbClosed.Visible = hasValue

This way is a switch and you will not need the if then. Null & "" = "". So it is an easy way to check both cas

Is the Closed a numeric or string. If numeric not need to check for "" but maybe O. I will assume closed is a string so check for both empty and null. Also I like to use a variable in this case to make things easier to read.

dim HasValue as boolean
    HasValue = (rs!closed & "") <> ""   'This will check both null and empty
    Me.Clstxt.Visible = not HasValue
    Me.cmbClosed.Visible = hasValue

This way is a switch and you will not need the if then. Null & "" = "". So it is an easy way to check both cases.

Is getting the same error.(I think this is why) There will be a lot of these clients/matters where the record does not exist in this query. So how can I check to see if the record exists and if not then make the txtbox visible and hide the combobox. Or is there a better way to do this?

 

[Gasman]

Use EOF or BOF as I mentioned.
You could use DLookup instead.?

Also I do not think Like can be used as ""

 

[why]

Use EOF or BOF as I mentioned.
You could use DLookup instead.?

Also I do not think Like can be used as ""

Thanks

 

[MajP]

There will be a lot of these clients/matters where the record does not exist in this query. So how can I check to see if the record exists and if not then make the txtbox visible and hide the combobox. Or is there a better way to do this?

Sorry. I thought you already fixed it based on post 2.

 

[why]

Sorry. I thought you already fixed it based on post 2.

No worries I should have. Thanks for your help

 

[Gasman]

I would DLookup?, much simpler.
If record does not exist value returned is Null ?

? isnull(dlookup("ID","Dates","ID=65"))

 

[Pat Hartman]

Other things:
1. "" = ZLS (Zero length STRING), NOT null. I don't know what the data types are for the bound field of the combo but numeric fields do not accept a value of ""
2. Always give your controls a proper name. Don't leave them as Combo193.
3. Always close a recordset after you open it.

 

[why]

Other things:
1. "" = ZLS (Zero length STRING), NOT null. I don't know what the data types are for the bound field of the combo but numeric fields do not accept a value of ""
2. Always give your controls a proper name. Don't leave them as Combo193.
3. Always close a recordset after you open it.

Thank you, you are right some of the code is pretty sloppy. It is a work in progress for now I switch to some higher priority parts of the project and will come back to this. Thanks for your help

 

[Pat Hartman]

Good practices need to be reflexive You don't apply them when you refactor.