Updating forms

[Trevor Howard]

Hello everyone

I have a “GoodsIn” form (linked to GoodsIn table) which holds common supplier data such as SupplierName and OrderNo’s, with a subform “Products” linked to “Products” table.

My “Products” subform is my form of interest, I enter new items in list form (all items entered on indexed numbers, and are entered as “one off” items, not as multiples)

Once items have been entered into the “Products” subform, I need them added to a Stock list, where items can be “picked off” for sale into a “Customer Sold” table with out removing the item from the Products table, which remains as a history of all items. Maybe the update should be done with a tick box?.

I have tried a one to one relationship, with mirrored tables, it updates on construction, but there after when items are added to “Products” they don’t update in Stock form.

I really need some guiding here!! Sorry, but I am not VB skilled.
Thanks for any help.

 

[yeatmanj]

In this particular situation, if I'm understanding correctly, I would use an SQL statement that appends the table if the record doesn't already exsist. If you are changing pricing or something, then I'd have an SQL statement that updates the table. It is much easier to append/update through SQL than it is to depend on access relationships to do it for you. I use SQL to run archives in a number of databases that I work with. Here is an example of an append statement:

DoCmd.RunSQL "INSERT INTO atblUtilities ( UtilityID, FacID, AcctNumber, MeterNumber, UtilityCompany, UtilityPhone, " _
& "UtilityExt, UtilityFax, UtilityAddress, UtilityCity, UtilityZip, UtilityContact, UserCreated, DateCreated, UserEdited, DateEdited ) " _
& "SELECT tblUtilities.UtilityID, tblUtilities.FacID, tblUtilities.AcctNumber, tblUtilities.MeterNumber, tblUtilities.UtilityCompany, " _
& "tblUtilities.UtilityPhone, tblUtilities.UtilityExt, tblUtilities.UtilityFax, tblUtilities.UtilityAddress, " _
& "tblUtilities.UtilityCity, tblUtilities.UtilityZip, tblUtilities.UtilityContact, tblUtilities.UserCreated, " _
& "tblUtilities.DateCreated, tblUtilities.UserEdited, tblUtilities.DateEdited " _
& "FROM tblUtilities WHERE tblUtilities.FacID = '" & strFacIDArchive & "';"

I hope I have been of some help to you in this. I do highly recommend doing what I think you are refering to through SQL statements instead of using access relationships. It is much more dependable and you have a lot more control over exactly what happens.

 

[Trevor Howard]

Hello Yeatmanj

Thanks for your reply, you have confirmed my suspicion that I am trying to do something beyond my ability. SQL is something I have no experience of sadly but will take a look. I think I will have to re-think my layout. If you or anyone else have any other suggestions I will be very grateful for any idea’s.
Thanks again!!
Trevor

 

[yeatmanj]

Don't be discouraged. It really isn't all that difficult. There is fairly thorough help in access on the subject. One other thing that may help you in the creation, is that you can create a query that accomplishes what you want using the access queries. If you then switch from design view to SQL view you can see the main syntax used for an SQL statement. You can also copy and past those to your VBA window and use DoCmd.RunSQL to initiate it. You have to remember to put the whole thing in quotes and change the quotes inside the phrase to single quotes. The only other thing, is if you look at the example I gave you will notice that I had to use line breaks to keep things in a single page format. That isn't necessary. You can make it one long string if it is easier for you.

Good luck.

 

[Trevor Howard]

Thanks for that!!! I am going to try it out. Sounds like just the information I needed.
Best wishes
Trevor