Auto Fill Fields with data from other tabel (1 Viewer)

[Marinus]

Hi Guys,

Nearly getting to the end of application with your help, I have a problem when writing the reports. I will try to explain my problem.
I have a form that creates a Weighbridge Docket, this form is called Touchscreen. It store Data in a table called Docket.

On this form I have a listbox Materials, it takes it's value from the Table Materials when clicked. I have a relationship between the ID's of Docket and Materials. The two other fields in the materials table are "buyprice" and "salesprice" when I work with current reports, it takes the value from the table materials. I can't use these values directly from the table materials as the price changes every so often. So I have to write these value's into my table Docket, so when making a report on i.e. Material Stock and the amount paid for the materials is equal to the real amount and not the last value's entered in the Materials table.

In short, could someone help me with some code that would read the buyprice value from the field in table Materials, and write this value in the buyprice field of the table Docket when a material is chosen from the listbox125.

Any help greatly appreciated..

 

[John Big Booty]

You could fire an Append query that uses;

Froms!YourFormName!ListBoxName

as criteria for selecting the correct price.

 

[Marinus]

You could fire an Append query that uses;

Forms!Touchscreen!ListBox125

as criteria for selecting the correct price.

Thanks for that John, problem is that I only want pricing to write in the current record and been banging my head on it for a week now..

The listbox as firing mechanism is a good idea, but it is the rest that I don't know and my book as advised by Moderator has not yet arrived yet..

Lame excuse

 

[John Big Booty]

First up create a select query, that selects the price from your price table, using;

Forms!Touchscreen!ListBox125

Now, this will return the value held in column(0) of your list box, which is probably hidden, and should hold the item ID, which can be matched to your price table.

If you wish to use one of the other columns in your list box as the criteria use;

Forms!Touchscreen!ListBox125.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X is the appropriate column number, remember that the columns are numbered from Zero up.

 

[John Big Booty]

...sorry, I got distract half way through writing that, and should have finished by mentioning that once you have the select query working to your satisfaction, convert it to an append query.

 

[Marinus]

First up create a select query, that selects the price from your price table, using;

Forms!Touchscreen!ListBox125

Now, this will return the value held in column(0) of your list box, which is probably hidden, and should hold the item ID, which can be matched to your price table.

If you wish to use one of the other columns in your list box as the criteria use;

Forms!Touchscreen!ListBox125.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X is the appropriate column number, remember that the columns are numbered from Zero up.

Thanks for that John, I am already using in my listbox, but this function will only display the data, actually I don't want the buyprice and salesprice displayed on the form, but their respective values read from the material table and in the background written to their fields in one record of the docket table when I pick one sort of material from the listbox.

This way, each record holds the prices for that moment, if the prices in the material table are changed then new records will have the new prices in them so I can calculate an average material stock value, of I can see what the selling price was for each record in docket and calculate an average selling price in a report.. I will attach a copy of the database maybe this is more clear.. password for marking a record as sale is 154, others can be found in table user_password.

Thank you so much for thinking with me..!!

 

[John Big Booty]

OK I've created a new query QRY_PriceAdd, have a look at that, it's currently only a select query, but open whilst you have your form Touchscreen open and see what happens. You should be able to easily change this to an append query. I didn't know what your destination table was or how you wanted to trigger it.

Incidentally the criteria is;

Forms!Touchscreen!List125

 

[Marinus]

Hi John, thanks for that, I have found out how to convert to a append query and when I run it, it adds the values I want from Material table to Docket Table, however it adds the values in a new row, rest of record is blank.. Will have to play some more to add the data in the correct row and where to fire the query without the append warning.. Probably in a OnClick event of the listbox when creating a new record.. maybe you have suggestion..
Cheers

 

[John Big Booty]

If you are looking for a query that adds data to an existing record, you want an Up Date Query, rather than an Append Query.

 

[Marinus]

If you are looking for a query that adds data to an existing record, you want an Up Date Query, rather than an Append Query.

Hi John, and thanks for the UpDate, I found this myself and tried today to make this update, however whatever I try I get 0 rows updated, I put the fields in the form and made sure that all properties were the same, made one - to many relationships and lost again.. Would you mind having a look??

 

[John Big Booty]

Can you save that back to '03 version? As I don't have Access '07 at my current location.

 

[John Big Booty]

Just as a quick tip for creating update or append queries. Start off by creating them as select queries, and ensuring that they are collecting the correct records and data, then convert that query to the action query of your choice.

 

[Marinus]

Just as a quick tip for creating update or append queries. Start off by creating them as select queries, and ensuring that they are collecting the correct records and data, then convert that query to the action query of your choice.

Thanks John, I tried to convert but it wouldn't allow me to save as 2003, I will follow your advise tomorrow.. here it is getting late, you have been a great help in learning.. Guess I will go on tomorrow..

One thing is sure.!! The answer is somewhere, we only have to find it..

 

[John Big Booty]

If I get a chance I have a look this evening when I get home.

 

[vbaInet]

Thanks John, I tried to convert but it wouldn't allow me to save as 2003, I will follow your advise tomorrow.. here it is getting late, you have been a great help in learning.. Guess I will go on tomorrow..

John I just tried to convert it. Marinus couldn't revert to '03 because somewhere in his db he's using some features only available to '07.

 

[John Big Booty]

John I just tried to convert it. Marinus couldn't revert to '03 because somewhere in his db he's using some features only available to '07.

Makes sense. If I get a chance I'll have a look at it this evening at home. But in all probability I won't get a chance till tomorrow sometime.

 

[vbaInet]

I was going to convert it and send to you but it's a shame it can't be done. Till tomorrow then

 

[John Big Booty]

OK You first Query QRY_PriceAddwasn't doing anything as it was simply updating itself to it's own current value.

QRY_PriceUpdate had unnecessary links to Table Docket (I think), I've removed that and believe it should now work as intended.

 

[John Big Booty]

Actually, I've just had another look at that query, and it's going to update the incorrect table (my bad ). However given that you are already storing the MaterialsID in your Docket Table, you really don't need to store prices as well. You can simply collect the price based on the materialsID you are already storing in the docket table.

 

[Marinus]

OK You first Query QRY_PriceAddwasn't doing anything as it was simply updating itself to it's own current value.

QRY_PriceUpdate had unnecessary links to Table Docket (I think), I've removed that and believe it should now work as intended.

Thanks John and vbaInet, the query function now works in updating the price of the material selected on the touchscreen when run, this function will seriously com in handy and will be used. However, it does not do what I would like it to do originally.

As you can see in the example Form!Touchscreen, I have put the fields BuySale and SalesPrice. I need this fields filled with the original prices from the material database. Once a record is created they should stay there, if a week later the price is changed in the material db I need to write the new prices in these fields so each record has the buyprice or salesprice for that moment.. Again, you all have been a great help and I hope I explained this function better now..