Update Single Record on Continuous Form (1 Viewer)

nstratton

Registered User.
Local time
Yesterday, 20:23
Joined
Aug 30, 2015
Messages
85
I have found this link to a question similar to one I have. Though after reading through it I cannot wrap my head around how it ended up working.

http://www.access-programmers.co.uk/forums/showthread.php?t=192900

In my situation, I've got an unbound form with a combobox that filters a subform (which is set to continuous). I have a combobox on the subform which will allow the user to change a contact's connection to a supplier.

I've added the primary key for the record but running the update query asks if I want to change all values. I believe my problem is I am not where how to incorporate a Me.Whatever to make this work as they have it in the link.

Not sure if the SQL is necessary but here it is anyway

Code:
UPDATE tblSuppliers INNER JOIN (tblContactType INNER JOIN tblContactInfo 
ON tblContactType.TypeID = tblContactInfo.TypeID) ON tblSuppliers.SupplierID = tblContactInfo.SupplierID 
SET tblSuppliers.Supplier = [Forms]![frmContactMenu]![frmContactDisplay]![cboChangeSupplier]
WHERE (((tblSuppliers.Supplier)=[Forms]![frmContactMenu]![cboSupplierContactSelect]));
 

Ranman256

Well-known member
Local time
Yesterday, 21:23
Joined
Apr 9, 2015
Messages
4,339
put the query in the combo box, cboClient, the list looks like (the user ONLY sees [CAPTION] field)

[caption], [query]
IBM, qsIbm
APPLE, qsApple

the user sees IBM, but afterupdate event of the combo, the subform using the query part, qsIbm. So the sub form shows only records from that query.

your update would use this too. Update [field] from cboClient where ...
 

nstratton

Registered User.
Local time
Yesterday, 20:23
Joined
Aug 30, 2015
Messages
85
I'm afraid I do not understand what you are getting at in how to do this.
I've attached a zip of my database. Everything is Access 2010 version.

I can upload another version if needed. I'm not asking anyone to just fill it out but if you go through it and can then point me in the right direction.

I want to be able to change supplier to a new supplier from the subform. The update query when opened asks to change all 4 records which is undesirable. Everything that is in the posted database contributes to this issue.
 

Attachments

  • Database2.accdb
    1.1 MB · Views: 94

nstratton

Registered User.
Local time
Yesterday, 20:23
Joined
Aug 30, 2015
Messages
85
This was actually much simpler than I was making it and I was able to figure out what I was doing wrong. Made the SupplierID field from the ContactInfo table a combobox in the form (in this case the subform) and set its rowsource to the Supplier table. Set the appropriate properties so the user can understand what they are looking at and works like a charm. This may have been what Ranman was saying to do but problem solved either way.
 

Users who are viewing this thread

Top Bottom