Say you have a form named frmMain. That form includes two fields in its record source: FigureID; and Figure_name. The form also includes a text box control named txtFigureID which is bound to the FigureID record source field.
frmMain also contains a subform control based on a form named frmSub. The record source for frmSub also includes FigureID and Figure_name fields. The subform control's link master/child field property is Figure_name. Therefore frmSub will display all the rows where Figure_name matches the corresponding value in the frmMain's current record.
Now, if you want frmSub to exclude the specific record (as identified by the unique FigureID value) which is the current record in frmMain, add a WHERE clause to frmSub's record source query:
WHERE FigureID <> Forms!frmMain!txtFigureID
I'm only guessing here, but hope that description is close enough to your actual situation to be useful. If not, show us the SQL you're using as the record source for your subform.
Edit: You get the parameter prompt only when you first open frmMain. Afterwards, you can navigate between records in frmMain, and frmSub shows you only the records you want to see ... without asking you again to supply a parameter value.
The reason that happens is because the subform loads before its parent form ... so a control on the parent form is not available when the subform loads.
I think the cure may be to save the subform without the WHERE condition in its record source. Then, when the main form loads, it can re-write the subform's record source to include the WHERE condition.
So, in frmMain's load event:
Private Sub Form_Load()
Dim strSql As String
strSql = "SELECT FigureID, Figure_name FROM YourTable" & vbCrLf & _
"WHERE FigureID <> Forms!frmMain!txtFigureID"
Debug.Print strSql
Me.subformControlName.Form.RecordSource = strSql
End Sub
Watch out for subformControlName. It's a control, not a form. The subform control may have the same name as the form it contains. But it could be a different name.
Make your target record the subform's current record. Then use RunCommand
to "select" it ... which will also highlight the record.
DoCmd.RunCommand acCmdSelectRecord
Seems like your question is morphing into "how do I make the last added record the current record".
If the last added record is still the current record, then you're there already, so no problem.
If the user navigated to a different record after adding the last one, there are at least 2 ways to get back to it.
DoCmd.GoToRecord
- Use a find method on the recordset clone.
Choose the approach which best fits your situation. If the bound value of your combo box is the numeric primary key for the row last added, you can try that second suggestion like this by replacing pkey_field
, YourComboNameHere
, and SubformControl
with the actual names you're using.
Private Sub FindLastRecordAdded()
Dim rst As DAO.Recordset
Dim strCriteria As String
strCriteria = "[pkey_field] =" & Me.YourComboNameHere
Set rst = Me.SubformControl.Form.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "Oops. This shouldn't happen.", vbInformation
Else
Me.SubformControl.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
Best Answer
If I’m reading this correctly, you’re saying you have the main parent table as the main part of the form, and you want the child records display in a sub form as you navigate around. Access works this way by default and you’ll not have to write any code at all.
And furthermore if you add child records to the sub form, the foreign key value will be set correctly for you. Again no code is needed.
So, build a standard form based on the main parent table. Then you can build a continuous form Based on the child table and drop that into the above main form.
You then open up the main form in desing mode and setup the sub form property sheet. In that property sheet simply set the link master and link child fields. The link master will be set to the primary key in the main form, and the link child is to be set = the foreign key field from the table in the sub-form.
Once the above is done, then the whole thing should work without any coding and all.
As for the child adding error message:
Assuming you have a main customer form, then the user can’t add a order until they navigate to a new customer record. If they don’t navigate to a new customer record then they will be editing an existing customer in this main form. So in all cases a main customer record will always be added before they can even enter a customer order.
I suppose a user could navigate to a blank customer record, then jump the cursor (focus) into the sub form where the orders are and start typing. Even in this scenario a blank customer record will be added when the focus moves from the main costumer form to the orders sub form. ( So the access UI handles the adding of the parent record when you’re using a classic main form + a sub form setup)
So from a user interface point of view, it’s not really going to be possible for the user to enter orders without a main customer record existing already. Because of this, you’re not going to get an error message about the parent customer record not existing. You’re only going to get this kind the error if the person opens up the orders form as a non sub form, and then tries to add a new order record. In this case you will get an get an error message. Therefore I really don’t see the need to trap this error message. I would not allow the user to open up the sub-form as stand alone. If for some strange reason you do allow the sub-form to be opened up separate from the main form, then open the form with allows additons = false (and again your prevent the need for the error message).
I suppose if for some The reasons users are allowed to open that’s sub form or the orders form separately and you’ve not turned off the allow additions, then they would be able to navigate to a new blank record, and that means the FK to the customer record would not be set correctly. If there’s provisions in this orders form to select a customer, then you could most certainly put the following code in the before update event of this orders form:
Once again in a sense were not really trapping the error message that a child record is about to be added without the parent record existing. We are simply using the UI interface to tell the user to do some action that will prevent that error message from ever occurring in the first place.