New Record and Key Value 1

[chappie2]

I have created the following code to open the appropriate linked record in a related table or to create a new record if a record does not exist.

Dim ProgType As String
ProgType = [Forms]![frmtest].[ProgramType]

If ProgType = "Competitive" Then
If IsNull(DLookup("[Grant#]", "tblCT", "[Grant#] = " & [Forms]![frmtest].[ID])) Then
If MsgBox("Create a new tracking record?", vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmCT", acNormal, , , acFormAdd
Else
MsgBox ("Action Cancelled")
End If
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "FrmCT", acNormal, , "[Grant#] = " & [Forms]![frmtest].[ID]
End If
End If


The issue is that if a new record is created, I want it to be created with the key value [tblCT].[Grant#] egual to the value of the record open in the original form [tblSum].[Grant#] in (frmtest). Can I do this in DoCmd.OpenForm acFormAdd or by setting a default value in the form that is being called? I haven't found anything that will work.

Thanks.

 

[willir]

chappie2

There are several ways to do this...

- A common approach is to reference the initial form directly
Me.YourIDField = [forms]![tblCT]![Grant#]
- A more versatile method is to use the OpenArg (open argument) approach where you pass the ID number/code as the last argument when openning the form. Look at Access help for DoCmd.OpenForm. Then on the target form, for the OnLoad event, have...

If Not IsNull(Me.OpenArg) Then
Me.YourIDField = Me.OpenArg
End If

This is more versatile since you can call the form from anywhere rather than hardcoding the source as in the first example.

- The third, perhaps most common approach is to use a form / subform approach. Much easier, and you can see the and retrieve the results together.

Richard

 

[OnTheFly]

Once the form is open for adding a record enter the Grant# in the appropriate control like

Forms!frmCT.txtGrant#=Forms!frmTest!Grant#



Hope this helps.

OnTheFly

 

[TheAceMan1]

How are ya chappie2 . . . . .

Note: If you should use [blue]OpenArgs[/blue] of the DoCmd (suggested by [blue]willir[/blue]), remember it [purple]only accepts a string[/purple]. So if the data you want to pass is numeric, it will require two conversions.

[blue]Str([/blue]Me.YourIDField[blue])[/blue] for OpenArg of the Docmd.

Val[blue]([/blue]Me.OpenArg[blue])[/blue] within the form.

See Ya! . . . . . .