refresh a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First of all Thank you all for your help.

I have a form based on a query. I have combo boxes for several different
fields in the query. In the criteria line of several of the fields I have
typed "[Forms]![GenSearchOT]![OTOfficercmb]" so to reference the contents of
that combo box.

My questions are:
If I have more than one of these combo boxes in a form how do I have the
query use just the ones with data selected?
How do I refresh the screen with the desired results?

Thanks
 
Joe,

In the query criteria, put like this...
[Forms]![GenSearchOT]![OTOfficercmb] Or
[Forms]![GenSearchOT]![OTOfficercmb] Is Null
Note that after you have done this and saved the query, next time you
open the design view of the query you will probably see that Access has
re-arranged the criteria to suit its own purposes - don't worry about
this :-).

In the After Update event of each combobox, you can put a line of code
like this...
Me.Requery
 
Steve,

I tried that. What it appears to be doing is looking for either the value
that is in the combo or a null value.

What I think I need is either the value in the combo box or disregard the
combo box. Does that make sense?

JC

Steve Schapel said:
Joe,

In the query criteria, put like this...
[Forms]![GenSearchOT]![OTOfficercmb] Or
[Forms]![GenSearchOT]![OTOfficercmb] Is Null
Note that after you have done this and saved the query, next time you
open the design view of the query you will probably see that Access has
re-arranged the criteria to suit its own purposes - don't worry about
this :-).

In the After Update event of each combobox, you can put a line of code
like this...
Me.Requery

--
Steve Schapel, Microsoft Access MVP

Joe said:
First of all Thank you all for your help.

I have a form based on a query. I have combo boxes for several different
fields in the query. In the criteria line of several of the fields I have
typed "[Forms]![GenSearchOT]![OTOfficercmb]" so to reference the contents of
that combo box.

My questions are:
If I have more than one of these combo boxes in a form how do I have the
query use just the ones with data selected?
How do I refresh the screen with the desired results?

Thanks
Click to expand...
Click to expand...
 
Joe,

Yes, it makes sense, and that is exactly what it should do. I can't
understand why it is not returning what you expect. Can you post back
with the SQL view of the query you have tried so far?
 
Steve,

It is returning the results of the combo 1 box and null.

SELECT [leave table].[Officer ID], tblrosterPersonal.[tblName last], [leave
table].[Week ending], [leave table].[Over Time Detail], [tblAccount
numbers1].[Accounts Name], tblrosterPersonal.[tblName first], [leave
table].Date, [leave table].[Date End], [leave table].[Overtime hours], [leave
table].[Date start]
FROM [tblAccount numbers1] RIGHT JOIN ([leave table] RIGHT JOIN
tblrosterPersonal ON [leave table].[Officer ID] = tblrosterPersonal.ID) ON
[tblAccount numbers1].[Account ID] = [leave table].[Account ID]
WHERE ((([leave table].[Officer ID])=[Forms]![GenSearchOT]![OTOfficercmb])
AND (([leave table].[Week ending])=[Forms]![GenSearchOT]![OTWeekEndingcmb] Or
([leave table].[Week ending]) Is Null))
ORDER BY tblrosterPersonal.[tblName last], [leave table].[Week ending];

Papi
 
Joe,

You didn't do it like I said. Please refer to my earlier post. The
criteria in the Week Ending field of the query needs to be entered like
this...
[Forms]![GenSearchOT]![OTWeekEndingcmb] Or
[Forms]![GenSearchOT]![OTWeekEndingcmb] Is Null
The above all goes in one line (just in case your newsreader word-wraps
it and makes it confusing). Remove the 'Is Null' in your existing
criteria. Let me know how you get on.
 
Steve,

It is amazing how well it works when you follow directions!!

I have a different question that I will post in the DB design section if I
need to.

I have a roster of students. These students naturally have siblings. What is
the best way to see the siblings on a given form. Do I need to create a
queary and relate the table to it's self?

Thank you for your help. I appreciate it.

Joe C
Papi


Steve Schapel said:
Joe,

You didn't do it like I said. Please refer to my earlier post. The
criteria in the Week Ending field of the query needs to be entered like
this...
[Forms]![GenSearchOT]![OTWeekEndingcmb] Or
[Forms]![GenSearchOT]![OTWeekEndingcmb] Is Null
The above all goes in one line (just in case your newsreader word-wraps
it and makes it confusing). Remove the 'Is Null' in your existing
criteria. Let me know how you get on.

--
Steve Schapel, Microsoft Access MVP


Joe said:
Steve,

It is returning the results of the combo 1 box and null.

SELECT [leave table].[Officer ID], tblrosterPersonal.[tblName last], [leave
table].[Week ending], [leave table].[Over Time Detail], [tblAccount
numbers1].[Accounts Name], tblrosterPersonal.[tblName first], [leave
table].Date, [leave table].[Date End], [leave table].[Overtime hours], [leave
table].[Date start]
FROM [tblAccount numbers1] RIGHT JOIN ([leave table] RIGHT JOIN
tblrosterPersonal ON [leave table].[Officer ID] = tblrosterPersonal.ID) ON
[tblAccount numbers1].[Account ID] = [leave table].[Account ID]
WHERE ((([leave table].[Officer ID])=[Forms]![GenSearchOT]![OTOfficercmb])
AND (([leave table].[Week ending])=[Forms]![GenSearchOT]![OTWeekEndingcmb] Or
([leave table].[Week ending]) Is Null))
ORDER BY tblrosterPersonal.[tblName last], [leave table].[Week ending];
Click to expand...
Click to expand...
 
Joe,

What is the design of your table so far? How is the sibling
relationship indicated in the data? Do you mean siblings where the
sibling is also a student? Or is this just an extra piece of data about
the student, whether the siblings are also students or not? If it's a
case of students who are siblings, then I would suggest an extra (?)
field in the table, FamilyID or some such, that identify the students as
belonging to the same family, and then you can use that field as
selection criteria to get the data for the form.
 
Back
Top