Syntax/Coding Direction Needed 1

[robmkimmons]

Hello!
I’m looking for some direction.
I believe that I’m doing “OK” on the relational data design, but am struggling with the syntax and details of the coding. I have the following tables created (see picture).

Essencially, I want a simple form with two unbound text boxes, [txtBadgeScan] and [txtItemScan]. If the item has not been previously checked-out by this associate then automatically check it out. However, if it has been previously checked-out, then check it back in.
Here’s my attempt:
Private Sub txtItemScan_AfterUpdate(Cancel As Integer)
Dim ItemScan As Variant
Dim ActionChoice As Variant
If Me.txtItemScan.Value = DLookup("AID", "tblTransactions", _
"[AID]=" & Me.txtItemScan.Value) And _
Me.txtBadgeScan.Value = DLookup(“AssocID”,tblTransactions”,_
“[AssocID]=”& Me.txtBadgeScan.Value) And_
Then
‘What do I do from here?
‘ This is where I don’t know what to do?
‘How do I create a new record in tblTransactions and give its _
‘ActionID field either a “1” Check-out or a “2” Check-in?

Couple of additional questions:
1) Should this form frmTransactions be unbound or bound to tblTransactions?
2) Would Case If in the coding be more efficient/effective?
Thank you for any input!


~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[fneily]

First some notes: not needed but may want to have a tblFacility in case Assoc. changes places or facility names changed. I've seen that before.
In tblAssociates you have AssocID and AssocNum. You can just use AssocNum as the primary key.
I have a feeling most of your primary keys are autonumbers. You may want to research why that's not such a good idea.

Let's say you have the following data in tblTransactions:
TID TDATE AID ASSOCID ActionID
1 2/1/07 Plate Bill 2
2 3/4/07 Plate Bill 1
3 5/5/08 Plate Bill 2
4 8/23/08 Plate Bill 1

So Bill took out a plate twice and returned it twice.

Create a form with two Unbound comboboxes. One for AssocID and the other for AID. I then placed a command button on the form, but you can use the AfterUpdate of the second combobox for the code coming up. Also create an Unbound textbox for TNotes.

You need to find the max transaction date of an AssocID/AID combination. Create a query from the tblTransaction with AssocID, AID, and TDate. Click on the Total Button that'll add a new row called Totals. Under AssocID and AID, it should say Group By. Under TDate, change it to Max.
On the Criteria line under AssocID, put:
[Forms]![Assoc_Aid_Form]![Combo0]
On the Criteria line under AID, put:
[Forms]![Assoc_Aid_Form]![Combo2]

The SQL view looks like:
SELECT tblTransactions.AssocID, tblTransactions.AID, Max(tblTransactions.TDate) AS MaxOfTDate
FROM tblTransactions
GROUP BY tblTransactions.AssocID, tblTransactions.AID
HAVING (((tblTransactions.AssocID)=[Forms]![Assoc_Aid_Form]![Combo0]) AND ((tblTransactions.AID)=[Forms]![Assoc_Aid_Form]![Combo2]));

Create another query taking the above query and match all three fields to the tblTransaction table. Bring down anyone of the fields and ActionID. Change it to a Make Table query.

The SQL view looks like:
SELECT tblTransactions.TDate, tblTransactions.AID, tblTransactions.AssocID, tblTransactions.ActionID INTO Action_Table
FROM tblTransactions INNER JOIN Assoc_Aid_query ON (tblTransactions.TDate = Assoc_Aid_query.MaxOfTDate) AND (tblTransactions.AID = Assoc_Aid_query.AID) AND (tblTransactions.AssocID = Assoc_Aid_query.AssocID);

This now creates one record that has the Max Transaction Date of that particular AssocID and AID and it'll show the last ActionID.

Now on the AfterUpdate event of the command button, put:
Private Sub Command4_Click()
Dim RS As DAO.Recordset, Q As DAO.Recordset
Dim SQLText
Dim UserSelection
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.OpenQuery "Assoc_Aid_query"
DoCmd.OpenQuery "Assoc_Aid_Action_query"
Set Q = CurrentDb.OpenRecordset("Action_Table", dbOpenDynaset)
If Q![ActionID].Value = 1 Then
UserSelection = MsgBox("Would You Lke To" & Chr(10) & "Check This Item Out?", vbYesNo)
Select Case UserSelection
Case 6
Set db = CurrentDb()
Set RS = db.OpenRecordset("tblTransactions", dbOpenDynaset)
RS.AddNew
RS![Tdate] = Date
RS![AssocID] = Me![Combo0]
RS![AID] = Me![Combo2]
RS![ActionID] = 2
RS![TNotes] = Me![Text5]
RS![ADueDate] = Date + 14
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
Case 7
End Select
Else
UserSelection = MsgBox("You Have This Item Checked Out." & Chr(10) & "Returning This Item?", vbYesNo)
Select Case UserSelection
Case 6
Set db = CurrentDb()
Set RS = db.OpenRecordset("tblTransactions", dbOpenDynaset)
RS.AddNew
RS![Tdate] = Date
RS![AssocID] = Me![Combo0]
RS![AID] = Me![Combo2]
RS![ActionID] = 1
RS![TNotes] = Me![Text5]
RS![ADueDate] = Date + 14
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
Case 7
End Select
End If
DoCmd.Echo True
DoCmd.SetWarnings True
End Sub

So user will select an AssocID and AID from comboboxes and type in some notes. They click the button. It'll run the two queries making a table with one record in it. It then checks the ActionID of this record and puts out an appropriate message/question. Depending on the users answer, it'll create a new transaction record indicating a checkout or checkin.