Make a form run off of a query?

[bowks14]

Hi all,

I am trying to make a form with fields that will run off of a query, rather than a table. Here is the problem. The field that dictates the query is set to "is not null" so I want only entries that have something in that field (it is a cell phone number field). When I go to add this field to the form, it only lets me select it from the table, thus adding records with no cell phone number, which I want to eliminate.

I am using Access 2007. This must be an easy fix, but I have limited knowledge on Access, so I came here!

Thanks in advance!

 

[RodgerDJr]

Bowks14,

Well, there are a few things I can see.

Are you wanting to use a drop down to move to a record? Using a query can lead to the form not being editable, so you really only want to use a query for display reasons.

To so this you would have an unbound combo box in the header or top part of the form and you would have a query here that would look at all the cell phones in the table, and here you can set it to only pull fields that are not null. Then we would put some code in the AfterUpdate Event to find that record.

But first I also noticed that your table is not normalized. There is no need to have a seperate field for each month. The way you have this setup is you will have to have another record next year. I would suggest breaking this into at least two tables. One with the customers information and then the second table would be all the billing infomation. Then on the form you would have a subform that links to the customer. Then you could add as many records as you want for that customer.

Rodger

 

[bowks14]

Rodger,

Thanks for your quick response!

I do not want to use a dropdown. I only want the records with cell phone numbers (not everyone has a cell phone in our department). What is the easiest way of doing this?

Ross

 

[RodgerDJr]

Rodger,

Thanks for your quick response!

I do not want to use a dropdown. I only want the records with cell phone numbers (not everyone has a cell phone in our department). What is the easiest way of doing this?

Ross

OK in that case you could have the record source for the form look at cell phone and in the criteria section make is is not null.

However I would still take the billing or amounts in a seperate table. In the new table you do not need to have a field for totals or any fields that calculate totals.

 

[bowks14]

Rodger,

The two are in seperate tables, but are on the same form. I have a one-to-one relationship between the two tables, but display all of the info on the form for easy entry.

I will try your suggestion and hopefully it will solve my problem! Thanks.

 

[RodgerDJr]

Ross,

I would make the table with all the months a many table. Add a field for month and year and remove your totals. Then on the sub form have a header with all the titles of the fields with the field under it. The fields that total we can put a formula in to add or preform what ever calculation you need.

Rodger