Auto Fill

[Scotty J]

Hi,



I haven't properly used access for about 10 years now, use to work on it really well, and time has certainly passed since.

But, I'm building a new database of our customers, and we do a hire scheme. The bit I'm stuck on, is I have two tables, one with all the customer details, and one with all the instrument details, I've got a combo box going that looks up the name of the instrument out of the "instrument" column on the "List of instruments" table, the record of the instrument is linked to it's price, I want this price to show upon selection of a instrument in a text box "Price", this "Price" text box is also linked to the "Customer" table so that information can be saved too. I'm struggling to create all the links really, got the combo showing correctly, but, no prices coming up in the text box.

I hope I've made this clear a mud...umm water hope someone can help.

Thanks in advance.

[hertfordkc]

See if "MyFormTest.Zip" helps. It is only intended to demonstrate one way of interacting with list boxes and combo boxes, so don't get hung up on what it was intended to do.
It should be available at this link.
https://www.accessforums.net/forms/f...xes-18492.html

[Scotty J]

Thanks for that, but, am still really struggling to get anything out of that.
I just can't get my head around it, if it was excel, I would have probably got it my now, linking the combo to give a result 1,2,3 etc, then an IF formula of =IF(A1=1,"C6,IF=(A1=2,"C7")) etc, damn access not understanding excel

[hertfordkc]

Thanks for that, but, am still really struggling to get anything out of that.
I just can't get my head around it, if it was excel, I would have probably got it my now, linking the combo to give a result 1,2,3 etc, then an IF formula of =IF(A1=1,"C6,IF=(A1=2,"C7")) etc, damn access not understanding excel

-------------------------------------------------------------------------
not with VBA. Generally speaking, to reference a query field in constructing a calculated field, you need to use brackets to enclose the field reference, e.g.
Newvar:iif([A1]=1,[C6],iif([A1]=2,[C7],"")) Sane concept applies when using VBA.
Also, in some instances you will need to qualify the field reference with an object reference, e.g. Forms![Myform]![A1] in order to remove ambiguity.
This link may be more helpful.
http://msdn.microsoft.com/en-us/library/ff191806.aspx

[alansidman]

Is this what you are trying to do?

http://www.baldyweb.com/Autofill.htm

Alan

[Scotty J]

Thanks, that's the coding I had, the last one:-

Me.TextboxName = Me.ComboName.Column(2)
Obviously, mines different:-

Me.Price = Me.Combo46.Column(2)

But still, when I select something within the instruments listing (Combo box), it doesn't change or even show a price in the "Price" text box.

I'm pulling my hair out because I thought I was right and something's going wrong and I can't see where

[alansidman]

Scotty J
I think its time to post a copy of your db with sample data so that we can look behind the curtains and see what is really happening.

[Scotty J]

Here you go, it's the "Hire Form" I'm struggling to get the "Make and Model" combo to change the "Price", it's all based off the "List of Instruments: Table", and I want the "Purchase column to display in "Price".

Eventually moving on to adding the "Hire Price" and "Minus 3" columns showing on two text boxes below that will also be saxes to the "Customer Database".

Thanks

[alansidman]

I have it displayed in your form. There is no reason to have it saved in your customer table as the information is already in your list of instruments table. This would be redundant and goes against the rules of normalization. If you need the information extracted for a report, it is always there and can be pulled using a query.

Here is a link to database design and dsicusses normalization.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[Scotty J]

Thanks for looking at that.

I'm wondering if I'm getting rather stupid, the list of instruments is displaying, but the price isn't changing when you select another instrument.

I need the prices's saved to the customer account as the prices change, so if customer "A" hires at "£79" a quarter, and a month later, customer "B" might hire it for "£89" per quarter.

I'm now wondering if I'm just complicating things now

[alansidman]

If the pricing is not static, then why do you have it in a lookup table. Should it not then be entered manually? Otherwise you will be updating the lookup table all the time and that is then creating redundant information. This is as I explained earlier contrary to normalization. Did you read the link on data base design and normalization?

Not sure what you are doing now, but in my example, the pricing changed with each change in selection of instrument. Compare your properties for your controls with the properties I established. I did change some of them.

Alan