Check whether control is empty

Topic

New Reply

I have two forms in a database. What I want to do is in a Name control in form 1, I want the text to be Red, if a control in form 2 contains data, but black if it is empty. I know how to refer to a control on the same form or subform, but not another form in the same database.

Reply | Quote

Replies

The syntax for referencing a form in Access VBA is Forms!FormName!ControlName so you could use the IsNull() function to test the content of the control in form2. Are you certain that Form2 will always be open?

Reply | Quote

Ha! I forgot that. No, form two won’t be open at the same time. Need to be able to check the table don’t I?

Reply | Quote

Good point – you cannot check the value of a control on a form if it isn’t open.

Reply | Quote

How do I check a field in a table? I’ve tried looking up DLookup, but can’t see anything that relates to my problem. From the form, I want to check if a field in the table has a Null value. If so, format the text in the form as black. If it contains data, format the text in the form to Red.

Reply | Quote

Rob,

I messed around with this and came up with this code which seems to work.

Code:

Option Compare Database
Option Explicit

Private Sub Form_Current()
  
  If Not CurrentProject.AllForms("Contracts1").IsLoaded Then
    MsgBox "The form Contracts1 is not currently loaded!", _
           vbOKOnly + vbInformation, "Error: Missing Form"
  End If
  
  If IsNull(Forms!Contracts1!FormName) Then
    Me!ReleaseDate.BackColor = RGB(0, 0, 0)
  Else
    Me!ReleaseDate.BackColor = RGB(255, 0, 0)
  End If

End Sub

Private Sub Form_Activate()

  Me.Refresh
  
End Sub

Note: The Refresh in the Form_Active routine does NOT work and it also doesn’t work if placed in a Form_GotFocus event! However, if you press F5 it works as it should, go figure! Hopefully, one of our Access Gurus will come up with a solution.

36211-red 36212-black
Of course you’ll have to adjust the Form & Field names as fit your situation. Note: the code goes in the form which gets the color change!
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for the code, but looking at it, doesn’t both forms have to be open? I don’t want two forms open. I don’t want to confuse the user. That’s why I need to search the table.

Reply | Quote

I don’t want two forms open. I don’t want to confuse the user. That’s why I need to search the table.

Is there no way I can check the value of a field in a table then?

Reply | Quote

Rob,

Yes both forms have to be loaded/open the code even checks to make sure the second form is loaded. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Rob,

Ok this code works for me.

Code:

Option Compare Database
Option Explicit

Private Sub Form_Current()
  
  If IsNull(DLookup("DeceasedDte", "Hospital", "[PatientNo] = " & Me!PatientNo)) Then
    Me!ReleaseDate.BackColor = RGB(0, 0, 0)
  Else
    Me!ReleaseDate.BackColor = RGB(255, 0, 0)
  End If
  
End Sub

[noparse]
The DLookup statement breaks down as follows:
DeceasedDte = The fieldname I want the value from.
Hospital = The data table where I’m searching
[PatientNo] = The fieldname I’m searching in Hospital.
Me!PatientNo = The search value from the form.

Note: beware of the quote marks as they are essential!
[/noparse]
Replace the above values with those appropriate to your situation.

Also don’t forget to replace the Me!ReleaseDate.BackColor statements with the field name of your form that gets the color change.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Ok, I’ve messed around with the tables, etc. and managed to get the data into the form using a query, so I have managed to get the control I need to check, in the form. I still can’t get anything to work using IsNull. I have a combo box (combo6) which selects the surname. A control on the form is called SIA. What I want is, after a Surname is selected, if control SIA contains any data, the background changes, but I can’t get it to work. I’ve got the Event in the After Update property of combo6, but I’m not sure if this is right.

Reply | Quote

Thanks retiredgeek. Didn’t see your reply until after I posted. I’ll try your method. It looks better than what I’m trying to do. Just one thing I’m not sure of, where do I put this code. As I said above, this is where I may be going wrong.

Reply | Quote

Sorry if I’m a bit thick but, I don’t understand the ‘The search value from the form’ bit. This is the code I’ve got

Code:

Option Compare Database
Option Explicit

Private Sub Form_Current()
  
  If IsNull(DLookup(“SIA”, “tblMaster Sheet”, “[FORNAME] = ” & Me!FORENAME)) Then
    Me!SURNAME.BackColor = RGB(0, 0, 0)
  Else
    Me!SURNAME.BackColor = RGB(255, 0, 0)
  End If


End Sub

but it’s not working. What I want to happen is when I select the surname from the combo, it checks the SIA in the tblMaster Sheet, to see if it contains data. If it does the background of the Surname is Red, if not, black.

Reply | Quote

Rob,

Ok, place the code in the AfterUpdate event of your combo box vs the Form_Current event! HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Still not working. Let me get this clear. DeceasedDte is a field on the table Hospital. PatientNo is a field on the table, and Me!Patient No is control on the form? So you’re checking that the patient numbers match, then check that the DeceasedDte is Null? Have I got it right?

Reply | Quote

Rob,

That is correct! Have you move the code to the ComboBox controls AfterUpdate event?

BTW: I forgot to mention that if you are checking on a name field you could have problems if you have more than one person with the same name. As I understand it DLookup will only find the 1st matching record in the DB.

Here’s the reference notice the limitation under the Remarks section. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for that. No matter what I tried, I couldn’t get it to work. Ended up adding the SIA number to the combo box, which apparently will do. Thanks for your assistance. I have another question relating to this database, which I will post in another thread.

Reply | Quote

I know this is an old thread but…

Everyone is fixed on the “empty” field being NULL, but what if it is just blank? Testing for nz(dlookup(…), ”) = ” (count those quotes) instead of isnull(dlookup(…)) might be what you needed.

Ian.

Reply | Quote

If FORMNAME is a text field you need to use
“[FORNAME] = ” & chr(34) & Me!FORENAME & chr(34)))

Reply | Quote