CODEWITHSUNDEEP
ms-access
HLEmerson
HLEmerson

Reputation: 27

Coding a button on a form in MS Access

I'm trying to build a database with MS Access. I have two tables- StockFrames and Projects, and I have a form- FrameCheckOut. On the form I have a FrameID field (where we will type in a frame id number or scan its barcode) and a ProjectName field, with a drop down of project names from the Projects table. I also have a button- Assign Frame. I want the button to update the StockFrames table with the projectID number so that I can know whether or not a frame is currently in use (or "checked out") to a project.

I have tried assigning this code to the button On Click: 

UPDATE StockFrames 
SET StockFrames.projectID = [SELECT Projects.projectID 
FROM Projects WHERE Projects.projectName LIKE projectName]
WHERE frameID = frameID;

.. but that code contains invalid syntax. I am very new to Access and coding and I would really appreciate some help if anyone is willing.

Upvotes: 2

Views: 233

Answers (1)

June7
June7

Reputation: 21389

Include key field in combobox (column can be hidden) RowSource. Value of combobox will be ID but users see and type name. Combobox properties:
RowSource: SELECT ProjectID, ProjectName FROM Projects ORDER BY ProjectName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"
ControlSource: leave blank if control is used to enter search criteria, otherwise field you want to save into

If form is bound to StockFrames, an UPDATE action is not needed. Find record for specific FrameID and simply select project from combobox.

If you prefer to use UPDATE, then have UNBOUND comboboxes for Frames and Projects designed as described above. Example VBA:

Private Sub AssignFrame_Click()
CurrentDb.Execute "UPDATE StockFrames SET ProjectID = " & Me.cbxProject & _
      " WHERE FrameID = " & Me.cbxFrame
End Sub

Upvotes: 0

Related Questions