rohrl77 rohrl77 - 4 months ago 9
SQL Question

How to create Access User Form to both look up and add data

I have an access form that contains two subforms. The main form has three dropdown fields that allow the user to pick what data is displayed in the two linked subforms. The dropdown fields are all unbound as they are referenced in the datasource of the main form.

The SQL of the datasource for the main form is as follows:

SELECT tbl_RptPeriod.RptPrdID, tbl_BusUnits.UnitID, tbl_Categories.CategoryID, tbl_Categories.CategoryTitle, tbl_Categories.CategoryGroup, tbl_Categories.Inactive, tbl_Categories.RptPrdID, tbl_Categories.UnitID
FROM tbl_RptPeriod INNER JOIN (tbl_BusUnits INNER JOIN tbl_Categories ON tbl_BusUnits.UnitID = tbl_Categories.UnitID) ON tbl_RptPeriod.RptPrdID = tbl_Categories.RptPrdID
WHERE (((tbl_RptPeriod.RptPrdID)=[Forms]![Compliance]![cmbReportPeriod]) AND ((tbl_BusUnits.UnitID)=[Forms]![Compliance]![cmbBusinessUnit]) AND ((tbl_Categories.CategoryID)=[Forms]![Compliance]![CategoryTitle]));


This works perfectly to look up records. However, I want the users to be able to add new records to the ones they are looking up. That doesn't work as it should.

I can add records, but then the form elements that I am using to look up the data, specifically
RptPrdID
,
UnitID
, and
CategoryID
are not being populated in the table, thus the new records are unassociated and don't show up if you go to look for them again in the main form after closing it or moving to another record.

This makes sense in so far as the form elements I am using in the "WHERE" criteria of the SQL are unbound, but of course, if I add a new record, I want it to be correctly matched. The user must be able to find the records he adds if he looks up the same criteria again.

Question:
How can I get new records entered in the subforms to have
RptPrdID
,
UnitID
, and
CategoryID
filled in?

Answer

Add code for filling those 3 fields in Before Insert event of main form

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.RptPrdID = Me.cmbReportPeriod
Me.UnitID = Me.cmbBusinessUnit
Me.CategoryID = Me.CategoryTitle
End Sub