Help with Tracking Database Form

[KCC47]

I am having issues with a new database I am developing.



The purpose is rather simple. My office receives applications in the mail. Before processing, these applications often must be sent inter-office to one of our other locations (we have several). We need a robust way to record receipt of the application initially, record the sending of it to another office, and receive it again at that office. It is essentially a tracking database that needs to record the when, why, where, and who of an internal mailing.

I've put all the tables together and most of the queries. I've also assembled most of the relevant forms, but the master form isn't working very well. It is the Application_Frm. After adding a dealer not in the list, the record disappears.

I also need assistance with the command buttons for tracking. I want three command buttons: send, receive, and tracking history. The send and receive command buttons will do essentially the same thing: open the Tracking_Entry_Frm with the tracking type prepopulated accordingly and the date already entered with today's date. The tracking history needs to show the full tracking history for that application.

I'm struggling on all fronts. Assistance and suggestions on improving the database would be appreciated.

[June7]

The main form DataEntry property is set to Yes. This means it cannot display existing records, only records added in current session.

I tested adding a new record. It remains available on the form but tabbing through last control in the TabOrder moves into a new record. If you don't want to allow that then change the form Cycle property.

[KCC47]

Well, that was a simple fix.

Minor issue, but why does the save command button in the dealer_add_frm not close the form?

I'm much more concerned about add the tracking send/receive functionality. I can't get the application_ID to pass to the tracking_entry_frm with the receive_commandbutton.

[June7]

There is no command in the Embedded Macro to close the form, it only saves record.

I don't use macros, only VBA.

I know VBA methods to pass value between forms. One is with the OpenArgs argument of DoCmd.OpenForm. Another is just to reference control on the Application_Frm. I don't know if macro can work with those.

[KCC47]

This is the VBA code I have for the receive command button now :

Private Sub Receive_CommandButton_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "Tracking_Entry_Frm", acNormal, , , acFormAdd, , Me!Application_ID
End Sub

It is not passing the application_ID to the transaction_entry_frm

[June7]

So you are using the OpenArgs method.

Now code in transaction_entry_frm must extract value from OpenArgs. Try in the form Current event like:

If IsNull(Me!Application_ID) Then Me!ApplicationID = Me.OpenArgs

I think the code to save record will cause the application form to refresh and the record you just saved will no longer be available on the form. Let me know if I am remembering that correct.

[KCC47]

That code worked.

In your fourt sentence, are you talking about saving the dealer_add_frm?

[June7]

No, the code in the Receive button Click event will save the application record.

[KCC47]

Is there a way to enter a value in a field on a form you are opening with a command button?

Ideally, I would like to select the receive command button or a send command button and simultaneously open the tracking_entry_frm and already have entered the type value. That field is set with only two options: recieve and send.


I'm also trying to create a query that will be the record source for a subform on my master form. The current record on the master form should display in this subform any/all matching records in the tracking_tbl. I've created a subfrm and query, but it displays all tracking records. I'm guessing I have the wrong joing type.

[June7]

Can use methods already described: OpenArgs or refer to control/field on first form.

Since there is no field indicating 'receive' or 'send' this could be passed in the OpenArgs argument then the opening form could extract.

If you want to send multiple info with OpenArgs, concatenate the data with a separater character such as a colon (:) then code in the opening form can use string manipulation to parse the data or LIKE operator to check for values in the OpenArgs string.

If Me.OpenArgs Like "*Receive*" Then
Me!fieldname = "Receive"
ElseIf Me.OpenArgs Like "*Send*" Then
Me!fieldname = "Send"
End If

Which form/subform involved in the issue?

[KCC47]

Which form/subform involved in the issue?

The Application_Tracking_History_Qry is the subform (I misnamed it in this your copy of the db.) It will be a subform on the Application_Frm. I know I haven't setup the query the subform is based on correctly.

[June7]

Looks like the same name to me.

No reason to include Application_tbl in the subform RecordSource. Need the Application_ID foreign key field then set Master/Child Links. Why use a query? Why not just use table as RecordSource as you did in the other subforms?

[KCC47]

Looks like the same name to me.

No reason to include Application_tbl in the subform RecordSource. Need the Application_ID foreign key field then set Master/Child Links. Why use a query? Why not just use table as RecordSource as you did in the other subforms?

That worked fine. Two fields in the subform only show the PK, Office_ID & Deputy_Clerk_ID. I've searched the properties but cannot find a way to only show other values in those tables, the text I need displayed.

[June7]

Should those two be comboboxes to select Office and Clerk from RowSource?

[KCC47]

That worked.

I'm attempting to add a search function to my master form. At the momemt I have a unbound combobox with a record source as vehicle_junction_tbl. I've added some code that doesn't appear to work. I can find the vehicle by the VIN, but it comes up with an error after update: Compile error method or data member not found.

Private Sub Vehicle_Search_ComboBox_AfterUpdate()
Dim rs As DAO.Recordset
If Not IsNull(Me.Vehicle_Search_ComboBox) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Vehicle_ID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub