Update Query for Specific Row in Table

[MCCDOM]

Hi there,

I was wondering what would be the best way of updating only a specific row in a table with new data from a form combobox?

Currently I have a query that updates the OfficeID and MailboxID in tblBreakdown to what ever has been selected in the corresponding comboboxes on frmMailboxAssignment. Trouble is this updates all the ID's for every record in tblBreakdown.
I would like it so it matches up the staffID selection in the combobox on the form with the record on the tblBreakdown and then only updates the OfficeID and MailboxID for that row please.

An example database is attached to show what I have done.

Thanks for your help,

Dom

 

[jdraw]

You need to identify the StaffID. You may need a dropdown/comb for Staff also.
Depends on your process for assigning "things".
I'm not sure I understand the purpose (really the name) of tblBreakdown.

 

[MCCDOM]

Hi Jdraw,

Thanks for the reply.

What I'm trying to accomplish is a database where I can assign a staffs email address to an office and what type of mailbox it is so I can break it down later to calculate the monthly cost for that office. The tblBreakdown is where I will store all the ID's for each criteria of the Mailbox allocation and then I can pull the relevant details like First Name, Last Name etc off of that ID list.

Hope that explains it clearly.

Thanks

Dom

 

[MCCDOM]

I think I've cracked it. Have managed to get it working for OfficeID using the following SQL Statement:

UPDATE tblBreakdown SET tblBreakdown.OfficeID = [Forms]![frmMailboxAssignment]![cboOffice]
WHERE ((([Forms]![frmMailboxAssignment]![cboName])=[tblBreakdown]![StaffID]));

Just need to get it to update the MailboxID. Is there a way to incorporate that into the above query or do I need to create a new query/

Thanks,

Dom

 

[Minty]

Assuming it's in the same table add the other field(s) to the set statement;

UPDATE yourTable SET Field1 = Value1 , Field2 = Value2 ,etc, etc
WHERE Your Criteria goes in here

 

[MCCDOM]

Yes that has worked Minty.

Cheers

Dom