Viewing Records in Form View

[AmyD.]

Hello, I am new to the whole forum thing, so please bear with me. I have been trying to figure this out for a while.

Ok. I created a form (Sales/EstFrm) from a table (Sales/EstTbl) using the form wizard. Everything is working fine except for one thing.

The main field that we would search by is Estimate Number. I want to, in form view, type in an estimate number in the Estimate Number field and have all the information about that Estimate number show up in the other fields in the form.

Instead, when i type in an estimate number in the estimate number field, it just changes the estimate number for the data that is already showing on the form.

I'm sure there is a simple solution, but i can not seem to figure it out. This is the one thing that is holding me back from completing this database. PLEASE HELP.

 

[shadow9449]

Hello, I am new to the whole forum thing, so please bear with me. I have been trying to figure this out for a while.

Ok. I created a form (Sales/EstFrm) from a table (Sales/EstTbl) using the form wizard. Everything is working fine except for one thing.

The main field that we would search by is Estimate Number. I want to, in form view, type in an estimate number in the Estimate Number field and have all the information about that Estimate number show up in the other fields in the form.

Instead, when i type in an estimate number in the estimate number field, it just changes the estimate number for the data that is already showing on the form.

I'm sure there is a simple solution, but i can not seem to figure it out. This is the one thing that is holding me back from completing this database. PLEASE HELP.

Hi, Amy

Did you try using a combo box? If you put one on your form, there's a wizard that allows you to use it for searches. That should suit your need.

SHADOW

 

[missinglinq]

Shadow's suggestion is the best way to handle this problem. I have to add that if your form and table is really named Sales/EstFrm and Sales/EstTbl, respectively, you're headed for disaster! The slash is one of the reserved special characters in Access and should never be used as part of an object name, as it will surely confuse Access! Better to use something like Sales_EstFrm and Sales_EstTbl or SalesEstFrm and SalesEstTbl! For a list of Reserved words and special characters goto:

http://support.microsoft.com/kb/286335

 

[AmyD.]

Thank you for your suggestions. I have changed the names on all tables and forms.
As for the combo box. I right clicked on the Estimate number feild in form design view and clicked Change To - Combo Box. Now it is a pull down box but no information is showing up in the combo box. Also, I may have a problem with the combo box idea. In this database, new records are always being added. i would like to be able to add records while in form view. Would the combo box create a problem?
Thanks for all your help!

 

[GaryPanic]

Your form need an extra combo box

use the wizard on this it will ask you what do you want to do and you want it to find a record based on the information

with the wizard take you time - understanding the wizard will save you hours in the long run..
a few other pointers for you

don't start field names with numbers ie 12fish if you do you will have to put [] round this and this is really annoying (found out the hard way)

certain words are reserved by Access such as date if you need to have a date field call it something like custDate or ProdDate Customer date / Product date but not date
try to steer away from spaces in field names so Customer Date would be come customerdate or custdate - no gaps-


g

 

[GaryPanic]

for your information / mean divide 1/2 = 0.5 or it could mean field value over another field value Access thinks you are trying to do calculations - and may have a tantrum...

 

[GaryPanic]

if combo boxs are "freaking you out a bit" they shouldn't - have a look in the samples section I think Bob(someone did anyway) did a bit on this that outlines how and why etc .

g

 

[missinglinq]

You need to convert your Estimate field back to a plain text box; converting like you did makes it a combobox but it has no ControlSource! Following Gary's idea, you need to add a combobox to your form.

First you must have your form set up to display the record you want to retrieve, which I assume you already have done. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up
Select "Find a record based on the value I selected in my combobox."
Hit Next.
Select the same query as your form is based on, then click on the field you're searching by to move it to the right side (EstimateNumber, in your case.)
Hit Next.
Size the column appropriately.
Hit Next.
Name the combobox.
Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type.
Hit <Enter> and the record will be retrieved.

Good Luck!

 

[AmyD.]

That sounds perfect. I will give it a try and let you know. Thanks guys!!!!

 

[AmyD.]

Sorry for the delay.
Well, it still isn't bringing the current information when i change the estimate number. Now when i push the next record button, the estimate number stays the same but all the other information changes. Before, i could scroll through the records using the "next record" button.
I just tried to upload the database but it keeps failing. Any other ideas without taking a look?

 

[AmyD.]

View attachment Sales-Estimating Form.zip
Here it is. Please let me know if you see anyting. Thank you so much!

 

[missinglinq]

Hey, Amy!

For some reason, Access doesn't like your EstimateNumber being formatted at the table level! I'm not sure why, since the field is a text field and the hyphen shouldn't matter, but apparently it does! I removed that table formatting then deleted your original combobox and ran the Combobox Wizard again and it retrieves the records fine now!

The EstimateNumber in the combobox isn't going to change when you navigate to a new record because it isn't bound to your record, it's just for retrieving a record. You need to place a bound control on your detail section to hold the EstimateNumber of the current record. If you want, you can format this control on the form level just like you did in the table, and it'll show your EstimateNumber as 07-023.

 

[AmyD.]

Thank you missinglinq! That is just how i wanted it to look up the records. i am having a different, but related problem now. It is looking up the values but for some reason, it is sometimes duplicating the Estimate Number in the combo box and then gives me the following error message: "Update or CancelUpdate without AddNew or Edit". Here is my database with the updates.
Sales - Estimating Form.mdb.zip
By the way, just so you know if they change when you are messing around with it, here are the estimate numbers with what job they should be with.
07-023 WSK
07-022 CCDT
07-021 CH
07-020 FWC
07-019 801AA
Thanks for all of your help!

 

[rainman89]

You only uploaded the link to the database. not the database itself!!!!

 

[AmyD.]

Oops! Sorry! Here it is.
Sales-Estimating Form.zip