How do i update some table records...

[shenty]

OK i have a database that i have been developing as and when i have time over the years. It has become more and more complex and now 7 or 8 others use it in the office too. I am self taught and have been enjoying doing it this way.

The db in question has become like a project management database. It has a table of contacts (tblAddress), a table of quotes (tblQuotes), a table of planning applications (tblPlanning) and a table of contracts (tblContracts). Between the 4 tables there is over 10,000 records.

I have various forms/queries that add records to each table dependant on what information is required. There is NO link between these tables but there are fields with similar information (ie Name, Phone Number, Sage Account Ref, Postcode etc.).

My current problem that i am trying to fix is the fact that when we search for information we have 4 different places we have to search (the 4 tables). I intend to manually create a link by adding an AddressID (AID) field to tblQuotes, tblPlanning & tblContracts. I will create 3 forms, one for each of these tables. The form will have the main address box on the left with a simple listbox and the respective table to the right in a multiselect listbox. I will have a Link button at the bottom. When pressed it will copy the UniqueID from the tblAddress to the AID field in the tblQuotes. That way i can select multiple quotes and link them to each contact in the address book.

I can do everything but the VBA code behind the Link button. Which is the HELP i need.

Thanks in advance

 

[shenty]

I think what i am trying to ask is:-

How do i use the rst.edit for each item selected in listbox.

This is the code i have come up with so far, it works in a fashion but is only setting the AID of the quotes table for the first item in the table/query. But i am not selecting this item in the list !!

Dim i As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qryQuotes")
For Each i In Me.listQuotes.ItemsSelected
rst.Edit
rst!AID = Me.listAddresses
rst.Update
Next i
Me.listQuotes.Requery

Set rst = Nothing
Set qd = Nothing

Any help would be great.

 

[boblarson]

You need a MoveNext in the recordset:

Set rst = dbs.OpenRecordset("qryQuotes")
For Each i In Me.listQuotes.ItemsSelected
rst.Edit
rst!AID = Me.listAddresses
rst.Update
[B][COLOR="Red"]rst.MoveNext[/COLOR][/B]
Next i

 

[shenty]

Hi Bob
Thanks for your response. I have added this and we are getting a bit further.

It is now adding the ID from the address to the AID of the quote qry for the correct number of records but it is starting from the top of the list and not JUST the items selected.

Would it help if i upload a sample db with the form, tables and query ?

Cheers
John

 

[shenty]

I have done a cut down db & uploaded.

I have also ran into another problem. When the button is clicked i get an error "Too few parameters. Expected 1".

For example on the left hand listbox i want to select "Ace Line Plant" and on the right hand listbox select ALL the "Ace Line Plant" quotes. (The filters are intended to help to some degree.

Then i want to click the button and 'post' the ID from the left hand listbox to the AID for all the selected items in the right hand listbox.

Thanks again.
John

 

[shenty]

Pat, firstly thanks for your response. The wording is what i've been trying to put across to the other users of the db. Trying to explain to them and myself why it would take a while.

The problems are deeper lying than that as there are 2 more tables i have not included in the sample db but with much the same type of data. Originally there were 4 very simple seperate databases. All they had was 1 table in each db. Gradually i have developed my db to combine them into 1 db. If i was starting from scratch i would do it in a manner you suggest. However as you say, there is a lot of data cleanup to do first.

With over 10,000 records split over the 4 tables it is not going to be an easy task. What i am trying to do is start the process with this form. I have decided against trying to be too clever and writing code that will 'automate' and find duplicates and update queries etc. hence the method i am using. Don't get me wrong i totally agree your method would be ideal but with so many different scenarios i just don't think it would be worthwhile.

I figured that because i am starting with existing data then i need to somehow link the records in quotes to the people in the address book. I would then do the same with the contract list & the planning list. By copying the AddressBookID to the AID field of the quote table this would be a good start.

Another problem (as you have noticed) is that not all the entries in the quote table have an entry in the main address table. The reason for this is (generally) because we would only added them to the main address table if the quote has been accepted by the client. There will also often be a number of different quotes for the same job to the same customer.

Similarly the main address book has many entries for people/companies/contacts that have never had a quote but may have come to us in the past for a 'yard sale' or one off purchase.

The same applies to the contract table and planning table. As the tables were originally totally independant and filled in by individuals for their own purpose, they all have different fields. My aim is to try and make data access more efficient.

So thats how it all started - now i'm at the "data cleanup" stage. By manually going through the quote table and matching them with an address ID i figured it was a good start....i sure as hell hope so !!

So how do i correct my code so that it posts the AddressBookID to the 'selected' records of the Quote Table listbox ?

Thanks again for your wise words and advice.

 

[shenty]

Still had no joy on this - could anyone help #?