m:
I already have the ItemType, ItemID, and WkDate captured - all
that remains is to query the data table against those values and
display the results in such a way that the user can easily edit
the data and click a button to overwrite the existing data
records.
You need to present a subset of data based on those three fields.
First, create a form that displays all the data from the returned
records, using "SELECT MyTable.* FROM MyTable" as your Recordsource.
Then you have a choice:
1. use the form's filter property to limit to the selected values,
OR
2. change the form's Recordsource.
I would tend to do the latter, and make the form's saved
Recordsource return one uneditable record. Here's the SQL to do
that:
SELECT TOP 1 Null As Field1, Null As Field2, Null As Field3
FROM MyTable
In that case, the field aliases (Field1, Field2, etc.) would be
replaced with the names of the fields in your actual table, so that
the bound controls will be displaying the Null from your default
recordsource.
What this accomplishes is that when you open the form, it displays
nothing but an empty, non-editable record (and you can't add
records, either, as it's bound to a Recordsource that to which you
can add no records).
To filter your records, you need 3 unbound controls for ItemType,
ItemID and WkDate. I would suggest that the first two should be
combo boxes, since there are clearly a limited number of choices for
that. You might also make WkDate a dropdown list, given that you are
only going to return data for dates that are in that field.
Then have a command button labeled FIND, and the event behind it
would be something like this:
Private Sub cmdFind_Click()
Dim strSQL As String
Dim strWhere As String
strSQL = "SELECT MyTable.* FROM MyTable "
strWhere = "MyTable.ItemType=" & Me!cmbItemType
strWhere = strWhere & "MyTable.ItemID=" & Me!cmbItemID
strWhere = "MyTable.WkDate=#" & Me!cmbWkDate & "#;"
Me.Recordsource = strSQL & strWhere
End Sub
Now, a few points:
1. You likely don't want the user to be able to click the FIND
button until all three fields have been filled out. So, you'd make
cmbFind disabled by default, and write a little Function:
Private Function EnableFind() As Boolean
EnableFind = (Not IsNull(Me!cmbItemType) _
And Not IsNull(Me!cmbItemID) _
And Not IsNull(Me!cmbWkDate))
Me!cmbFind = EnableFind
End Function
Then you can have the AfterUpdate event of all three criteria
controls be this function. To do that, in design view click on
cmbItemType, then SHIFT-click cmbItemID and cmbWkDate in turn. This
will select all three controls. Then on the Events tab of the
property sheet, type =EnableFind(). This will fire the check to see
if the three fields are filled out for the AfterUpdate of each of
the, and when all three are filled out, the FIND button will be
enabled.
2. You probably don't want to set the Recordsource to something that
will return no records. You have two choices for how to accomplish
this:
a. filter each combo box in sucession. In other words, when you
choose a value for cmbItemType, filter cmbItemID and cmbWkDate to
return only those values that match cmbItemType. When you've
chosen the value for the second dropdown lise, filter the dates to
limit to those that are value for the first two criteria.
b. in cmdFind_Click, add a check for the filter criteria. That
would be something like this:
If DCount("MyTable", "*", strWhere) = 0 Then
MsgBox "No records that match the chosen criteria.", _
vbExclamation,"Note"
Else
Me.Recordsource = strSQL & strWhere
End If
Depending on the data, I would probably choose the first, as I don't
like to give users the opportunity to put in search criteria that
return nothing (and disappoint the user), unless the cost of
checking ahead is high.
One last consideration:
ItemType and ItemID raise my suspicions about what you're liking
for. I don't know why a user would ever need to know the ItemID of
something -- that ItemID represents some real-world thing, right?
So, in that case, while I might filter for the underlying ItemID, I
would never ask the user to put that in. Instead, I'd give the user
the human-friendly item name from the combo box and have the ItemID
be the bound colum of the combo box (which is what you'd filter on).
