Use form input in an Update Query (1 Viewer)

Hi everyone,

This is my first post, so thanks in advance for any help!

I'm a little stuck on what I'm sure must be a pretty simple Access query. Here's what I'm after:

I have a data table with 2 columns - location and part number. Location is the primary key and so all locations are unique. What I need to do is to add or update a part number to the part number field, for the location which is selected on the form. I've so far managed to get a select query to return the location selected on the form, but I'm a bit stuck on how to get the query to also pull in the part number input on the same form, when I change this to an update query. I've attached where I'm up to so far.

Any help on this one would be hugely welcome, thanks guys!

Mark.

First, welcome to the forums.

I have made many changes to your database.

Among the changes that I made are:
Field Names in your table had spaces - removed the spaces
Changed the PartNumber field from a Number type to a Text type field
Controls on your form were not bound to the record source.
Added a few controls and some VBA code to add functionality.

I have attached a modified version of your database. I have some VBA code behind some of the events of your form and the control on the form. I have tried to provide extensive comments within this VBA code so you can hopefully understand what the code does. Take a look around in it and when you have any questions please post back.

Mr B,

Thanks so much for your help, and your v well commented VBA code! It works, now all I need to do is work out how!

I think I'm steadily realising that VBA can't be avoided if you really want Access to do what you want!