Form to Edit Records - Selecting Records To Edit

[FSHOTT]

I am designing a form to edit purchasing records and I am looking for a way
to home in on the record to be edited. From Combo Boxes (or list boxes) I
would to select the Year, Supplier, and Month to get into the correct range
of records so one could quickly scroll to the record needing editing. Can
someone help with methods to do this? Thanks ahead of time.....

 

[Dirk Goldgar]

I am designing a form to edit purchasing records and I am looking for a way
to home in on the record to be edited. From Combo Boxes (or list boxes) I
would to select the Year, Supplier, and Month to get into the correct
range
of records so one could quickly scroll to the record needing editing. Can
someone help with methods to do this? Thanks ahead of time.....

Put those combo or list boxes in the Form Header. Set the form's
RecordSource query so that it refers to those controls for criteria, but
also allows for the controls to be Null. Thus, if your form were named
"frmPurchasing", you might have a query along the lines of this:

SELECT * FROM tblPurchasing WHERE
(Year(PurchaseDate) = Forms!frmPurchasing!cboYear
OR Forms!frmPurchasing!cboYear Is Null)
AND
(Month(PurchaseDate) = Forms!frmPurchasing!cboMonth
OR Forms!frmPurchasing!cboMonth Is Null)
AND
(Supplier = Forms!frmPurchasing!cboSupplier
OR Forms!frmPurchasing!cboSupplier Is Null);

Then use the AfterUpdate event of each of the three filtering controls
(cboYear, cboMonth, and cboSupplier in the above example) to requery the
form:

Private Sub cboYear_AfterUpdate()
Me.Requery
End Sub

Private Sub cboMonth_AfterUpdate()
Me.Requery
End Sub

Private Sub cboSupplier_AfterUpdate()
Me.Requery
End Sub

That's all you'd have to do. There are other ways to go about it, but this
is pretty simple.

 

[Dirk Goldgar]

I am designing a form to edit purchasing records and I am looking for a way
to home in on the record to be edited. From Combo Boxes (or list boxes) I
would to select the Year, Supplier, and Month to get into the correct
range
of records so one could quickly scroll to the record needing editing. Can
someone help with methods to do this? Thanks ahead of time.....

Put those combo or list boxes in the Form Header. Set the form's
RecordSource query so that it refers to those controls for criteria, but
also allows for the controls to be Null. Thus, if your form were named
"frmPurchasing", you might have a query along the lines of this:

SELECT * FROM tblPurchasing WHERE
(Year(PurchaseDate) = Forms!frmPurchasing!cboYear
OR Forms!frmPurchasing!cboYear Is Null)
AND
(Month(PurchaseDate) = Forms!frmPurchasing!cboMonth
OR Forms!frmPurchasing!cboMonth Is Null)
AND
(Supplier = Forms!frmPurchasing!cboSupplier
OR Forms!frmPurchasing!cboSupplier Is Null);

Then use the AfterUpdate event of each of the three filtering controls
(cboYear, cboMonth, and cboSupplier in the above example) to requery the
form:

Private Sub cboYear_AfterUpdate()
Me.Requery
End Sub

Private Sub cboMonth_AfterUpdate()
Me.Requery
End Sub

Private Sub cboSupplier_AfterUpdate()
Me.Requery
End Sub

That's all you'd have to do. There are other ways to go about it, but this
is pretty simple.

 

[FSHOTT]

Dirk Thank You very much for your direction it worked Great. Unfortunately I
have another problem. I need help on when I use the SELECT statements below
you recommended, I find I am unable to Edit/Change some of the fields in the
detail section of the form. I am able to change the fields in the subforms I
bring into the main form (they are fields in the related table queries) but
not the fields in the qryPurchasingTable query. I was using a SELECT DISTINCT
SWSRecordNo, * and thought that was the problem but I got rid of the DISTINCT
comand and still cannot alter the fields. The purpose of this form is to
Review and Edit data inputted to the PurchasingTable and related Late &
Reject Tables. I appreciate any direction that can be provided.

 

[Dirk Goldgar]

Dirk Thank You very much for your direction it worked Great. Unfortunately
I
have another problem. I need help on when I use the SELECT statements
below
you recommended, I find I am unable to Edit/Change some of the fields in
the
detail section of the form. I am able to change the fields in the subforms
I
bring into the main form (they are fields in the related table queries)
but
not the fields in the qryPurchasingTable query. I was using a SELECT
DISTINCT
SWSRecordNo, * and thought that was the problem but I got rid of the
DISTINCT
comand and still cannot alter the fields. The purpose of this form is to
Review and Edit data inputted to the PurchasingTable and related Late &
Reject Tables. I appreciate any direction that can be provided.

I don't have quite enough information to know what's going on. The query I
posted ought to be updatable. Is qryPurchasingTable the form's
RecordSource, or is the recordsource a query (whether stored or an inline
SQL statement) that selects from qryPurchasingTable. Either way, please
post the SQL of all queries involved.

Also, what exactly do you mean when you say "I am unable to Edit/Change some
of the fields"? Does that mean you are able to edit some controls but not
others? Is it that you can't edit bound controls but can edit unbound ones,
or are there some bound contols you can edit? If the latter, which controls
are you able to edit, and what are their ControlSources?

 

[FSHOTT]

Dirk Thank for you attention to my Questions. Hopefully I can provide the
info to make this sowewhat more clear. The Record Source statement in my
Edit Form is as follows:
Record Source for Purchasing Data Edit Form
SELECT DISTINCT SWSRecordNo, * FROM qryPurchasingTable3 WHERE
((MyYear=Forms!frmDataEditForm1!cboYear) Or (Forms!frmDataEditForm1!cboYear
Is Null)) And ((MyMonth=Forms!frmDataEditForm1!cboMonth) Or
(Forms!frmDataEditForm1!cboMonth Is Null)) And
(SupplierNo=(Left(Forms!frmDataEditForm1!cboSupplier,6)) Or
((Left(Forms!frmDataEditForm1!cboSupplier,6)) Is Null));

The VBA code for the Query used in the above statement - i.e. for
qryPurchasingTable3 is as follows:

qryPurchasingTable3 - Select Query
SELECT [Purchasing Table].RecordID, [Purchasing Table].SWSRecordNo,
[Purchasing Table].DataEntryDate, Year([DataEntryDate]) AS MyYear,
MonthName(Month([DataEntryDate])) AS MyMonth, Month([DataEntryDate]) AS
MonthNo, [Purchasing Table].SupplierNo, [Supplier Table].SupplierName AS
[Supplier Name], [Purchasing Table].Lots, [Purchasing Table].EarlyLots,
[Purchasing Table].LateLots, [Purchasing Table].RejectLots,
[PurchasingTable].TotalQuantity, [Purchasing Table].RejectQuantity
FROM [Supplier Table] INNER JOIN ((([Purchasing Table] LEFT JOIN [Early
Table] ON [Purchasing Table].SWSRecordNo = [Early Table].SWSRecordNo) LEFT
JOIN [Late Table] ON [Purchasing Table].SWSRecordNo =
[LateTable].SWSRecordNo) LEFT JOIN [Reject Table] ON [Purchasing
Table].SWSRecordNo = [Reject Table].SWSRecordNo) ON [Supplier
Table].SupplierNo = [Purchasing Table].SupplierNo
ORDER BY [Purchasing Table].SWSRecordNo;

I also tried to attach 3 Paint files which are screen pictures of the
qryPurchasingTable3 Desing View, The Design View of the frmDataEditForm1 and
the same screen capture with the Properties view for the form. That didn't
work. If these would be helpful and there is a way to e-mail them let me
know.

I am able to edit the bound controls in the subforms I have included in the
main form but not able to edit the bound controls in the main form. The
ControlSources for the bound controls (that I can edit) on the subforms are
fields from the LateTable and the RejectTable. These Tables are related to
the PurchasingTable by the SWSRecordNo.


The ControlSources for bound controls that I can't edit are the fields in
the qryPurchasingTable3.

Boy do I hope this helps! Thanks....

 

[Dirk Goldgar]

Dirk Thank for you attention to my Questions. Hopefully I can provide the
info to make this sowewhat more clear. The Record Source statement in my
Edit Form is as follows:
Record Source for Purchasing Data Edit Form
SELECT DISTINCT SWSRecordNo, * FROM qryPurchasingTable3 WHERE
((MyYear=Forms!frmDataEditForm1!cboYear) Or
(Forms!frmDataEditForm1!cboYear
Is Null)) And ((MyMonth=Forms!frmDataEditForm1!cboMonth) Or
(Forms!frmDataEditForm1!cboMonth Is Null)) And
(SupplierNo=(Left(Forms!frmDataEditForm1!cboSupplier,6)) Or
((Left(Forms!frmDataEditForm1!cboSupplier,6)) Is Null));

There's your problem right there: "SELECT DISTINCT". Any time you use the
DISTINCT keyword in a query, the results will not be updatabase. You only
use DISTINCT when you want to remove duplicates in your result set, but
doing so renders the query nonupdatable, because any field value in the
result set can no longer be traced back to a single record in a table.

Do you have a need to remove duplicates? If so, then there is no way to
make this form updatabe. Assuming you don't, then try this SQL for the
RecordSource instead:

SELECT * FROM qryPurchasingTable3
WHERE
((MyYear=Forms!frmDataEditForm1!cboYear)
Or (Forms!frmDataEditForm1!cboYear Is Null))
And ((MyMonth=Forms!frmDataEditForm1!cboMonth)
Or (Forms!frmDataEditForm1!cboMonth Is Null))
And ((SupplierNo=Left(Forms!frmDataEditForm1!cboSupplier,6))
Or (Forms!frmDataEditForm1!cboSupplier Is Null));

I simplified it a bit, as well as removing the DISTINCT keyword. I may have
messed up those parentheses, though, so don't panic if that doesn't work.