Fill in blanks controls on form

[shangman]

I have a form which i use combo boxes to select info on the form from querys.
As i select from the first control the form will requery for the next
selection. (total of 2) After the second selection the form automatically
fills in the rest of the controls. (total of 3) based on my 2 selections.
This all works find. The problem i am having is sometimes the last control
could be blank. Because no info found during the requry. What i would like it
to do is to fill in the blank with the word "none". I tried using the if
statement (example below). Could someone help me on this? It's probably
something simple and i just didn't quite figure it out.

Private Sub Fname_AfterUpdate()
Me.clocknum.Requery
Me.fullname.Requery
Me.jcode.Requery
Me.List380.Requery
If IsNull(Me.List380) Then
Me.List380 = "none"
End If
End Sub
This didn't work, i dont get an error it just doesn't put in the word "none".
Any help would be greatly appreciated.

 

[Chegu Tom]

It could be that your List380 contains an empty string rather than a null

Try
if isnull(me.list380 OR me.list380=""

Or you can go to the code that fills those boxes in the first place and put
an if statement there

 

[Jeanette Cunningham]

Hi shangman,
One way is to create a union query for the row source for list380, so that
'none' is available to be selected from the list.

Another option could be to have a label that says 'none'.
Put the label behind the listbox.
If the requery leaves list380 with no data, hide the listbox and user will
see the label that says none.

Alternatively you could have a permanent label above the listbox, and set
the label's caption to 'none' whenever the list box has no data.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[shangman via AccessMonster.com]

When you say to hide the list box, is this done manually or automatic when
list380 is blank? I did figure out something similar on how to do that, but i
had to refresh the form to make it work after the requeries. How can i have
it hide automatically after i enter my info into the controls and the result
is blank? This probably something simple to do, but i am at a lost.
For the union query, how would i get it to work by selecting "none" if
list380 is blank?
Thanx for your help!

Hi shangman,
One way is to create a union query for the row source for list380, so that
'none' is available to be selected from the list.

Another option could be to have a label that says 'none'.
Put the label behind the listbox.
If the requery leaves list380 with no data, hide the listbox and user will
see the label that says none.

Alternatively you could have a permanent label above the listbox, and set
the label's caption to 'none' whenever the list box has no data.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a form which i use combo boxes to select info on the form from
querys.

[quoted text clipped - 20 lines]

"none".
Any help would be greatly appreciated.

 

[shangman via AccessMonster.com]

Thanx for the info, but when i tried what you suggested it errors. In the
form i enter info into one of the list boxes(Lname), then the next one(Fname),
when the 2nd one is enter the form requerys to enter the other info for the
next four boxes:

clocknum
fullname
jcode
(these three are text boxes, i use dlookup for these based on what i selected
in Lname & Fname)

list380
(is a list box, which uses (SELECT secondary_jobs.[job code] FROM
secondary_jobs WHERE (((secondary_jobs.employee)=Forms!frmSTtest!fullname));

list380 is a list box because there may be more than one finding (up to 10)
but this could be blank, thats when i want the verbiage "none" to appear in
this list box.

It could be that your List380 contains an empty string rather than a null

Try
if isnull(me.list380 OR me.list380=""

Or you can go to the code that fills those boxes in the first place and put
an if statement there

I have a form which i use combo boxes to select info on the form from
querys.

[quoted text clipped - 20 lines]

"none".
Any help would be greatly appreciated.

 

[Jeanette Cunningham]

You can find out if the listbox has no records to show like this.

If Me.List380.ListCount <=0 Then
'no records
Me.List380.Visible = False
Else
Me.List380.Visible = True
End If

The above code will hide the listbox when it has no records.
You need to put that code where it will be called whenever the data for the
form changes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

When you say to hide the list box, is this done manually or automatic when
list380 is blank? I did figure out something similar on how to do that,
but i
had to refresh the form to make it work after the requeries. How can i
have
it hide automatically after i enter my info into the controls and the
result
is blank? This probably something simple to do, but i am at a lost.
For the union query, how would i get it to work by selecting "none" if
list380 is blank?
Thanx for your help!

Hi shangman,
One way is to create a union query for the row source for list380, so that
'none' is available to be selected from the list.

Another option could be to have a label that says 'none'.
Put the label behind the listbox.
If the requery leaves list380 with no data, hide the listbox and user will
see the label that says none.

Alternatively you could have a permanent label above the listbox, and set
the label's caption to 'none' whenever the list box has no data.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a form which i use combo boxes to select info on the form from
querys.

[quoted text clipped - 20 lines]

"none".
Any help would be greatly appreciated.

 

[shangman via AccessMonster.com]

Thanx so much for the help it works perfectly!!!

You can find out if the listbox has no records to show like this.

If Me.List380.ListCount <=0 Then
'no records
Me.List380.Visible = False
Else
Me.List380.Visible = True
End If

The above code will hide the listbox when it has no records.
You need to put that code where it will be called whenever the data for the
form changes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

When you say to hide the list box, is this done manually or automatic when
list380 is blank? I did figure out something similar on how to do that,

[quoted text clipped - 27 lines]