Auto Fill

[lamarrgodwyn]

Hello I am pretty new to Access. I have a form with a table in it that I would like to do an auto fill. The two fields I'm working with is Part number and ECPS Cost. The Part number field is a list box field that show 3 fields(part number, description, and part cost) the part number is the Primary key. I want the ECPS COST field to auto fill with the part cost when you select the part number. thanks in advance for you help with this.

 

[John Big Booty]

First up welcome to the forum.

To do what you want, in your text boxes put something like;

=Me.ListBoxName.Column([B]x[/B])

Were x is the column in the list box that hold the info you want in that text box. Remember that the columns in list and Combo boxes are numbered from Zero up, so the first column (usually hidden) is column(0)

 

[lamarrgodwyn]

I'm really a beginner at this. I picking up where someone else left off. can you explain in more detail

 

[John Big Booty]

OK that bit of code needs to go into the text boxes Control Source if you open the form in design view you will find it on the Data tab of the properties window. Alternately you could just type it directly into the text box whilst the form is in design view.

=Me.[B][COLOR="DarkOrchid"]ListBoxName[/COLOR][/B].Column(x)

You will need to replace ListBoxName with the name of your list box (possibly it is called List2 or similar) to find the name click on the list box whilst form is in design view and check the first field of the All tab in the properties window.

The other thing you will need to change is the "x" in Column(x) as mentioned previously. everything else will remain unchanged.

 

[lamarrgodwyn]

For some reason design view is greyed out. I do have to hold down shift when i open it to edit it. But for some reason the Design view is greyed out for forms and reports. I cant edit tables and queries. Is there something that I can do to fix this?

 

[boblarson]

Are you sure you haven't opened an MDE or ACCDE file instead of an MDB or ACCDB?

 

[lamarrgodwyn]

the file ext is mdb

 

[lamarrgodwyn]

Sorry i checked and it really a MDE file. I had it converted back to a MDB file. But the Formular still will not work.

 

[John Big Booty]

Any chance you can post a copy of your DB?

 

[boblarson]

Sorry i checked and it really a MDE file. I had it converted back to a MDB file. But the Formular still will not work.

You can't convert an mde file back to an mdb file. At least not without special help from an expert and pay big bucks. My guess is you renamed the file but it is still an MDE file.

 

[lamarrgodwyn]

hello john i can post the file but i dont know how what do i need to do

 

[boblarson]

hello john i can post the file but i dont know how what do i need to do

How to upload a database to the forum

 

[lamarrgodwyn]

Here is a copy of the database that I need help with. I think I accidently converted it to a MBE file. If possible I need to have it converted back to a MDB file. I would like for the ECPS cost to automatic populate the cost of the item once it is selected. Also I would like for the charges to be subtracted from the balance when the waive box is checked. Thanks in advance for all of your endeavors.

 

[boblarson]

Yep, that is an MDE file. No changes can be made to forms, reports, macros or modules. You will need to find the MDB file in order to make changes. If the mdb file is lost, you can try contacting Wayne at EverythingAccess.com to see how much it would cost to convert it back (it won't be cheap).

 

[lamarrgodwyn]

hello you guys thanks a lot for your help in the past. I found a old back up copy of the DB and it is a MDB file. So I wanted to see if you could still help me with this issues. I have a form with a table in it that I would like to do an auto fill. The two fields I'm working with is Part number and ECPS Cost. The Part number field is a list box field that show 3 fields(part number, description, and part cost) the part number is the Primary key. I want the ECPS COST field to auto fill with the part cost when you select the part number. thanks in advance for you help with this.

 

[ericryd]

hello you guys thanks a lot for your help in the past. I found a old back up copy of the DB and it is a MDB file. So I wanted to see if you could still help me with this issues. I have a form with a table in it that I would like to do an auto fill. The two fields I'm working with is Part number and ECPS Cost. The Part number field is a list box field that show 3 fields(part number, description, and part cost) the part number is the Primary key. I want the ECPS COST field to auto fill with the part cost when you select the part number. thanks in advance for you help with this.

I'll take a stab at it...

Is all of the pricing data in the table?

If so, let's call the ECPS COST field [Price] and we'll call the list box [PartNumber].

[Price]should have a Row Source something like: SELECT PartsQuery.Price, PartsQuery.PartNumber FROM PartsQuery WHERE (((PartsQuery.PartNumber)=[PartNumber]))

The second part is going to the [PartNumber] box and changing the AfterUpdate (code) and put: Me.Price.Requery. That way, once you click something in the list, it will refresh the [Price] field.

That's how I would do it and have done it in the past. Someone may have a better suggestion..

 

[dkinley]

Jumping in here to maybe provide some quick help.

I am attaching a sample db I picked up or made (can't recall, apology if someone else did). This will get allow you to dissect it to see what is going on.

Also, there is some explanation on this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=188409

As Big John said, ensure your references are correct. If I could emphasize anything in the properties it would be - Column Count, Column Format, Bound Column. And for the auto-filling it would be as Big John said, the columns start at 0.

Hope that helps,
-dK