Subform question: open record or new record

[Real Wally]

Hi all,

I've got a form with a subform (table).
Subform has a field ApplicationNum. By clicking on that field a subsequent form opens with application details (frmApplication). The linking field is AppID. Works fine, no problem.
However, a problem occurs when there is no application detail form for an ApplicationNum.

What I'd like to happen in that situation is that a new application detail record opens that automattically copies the AppID number from the subform.

Who's got the golden tip?
What I've got so far in the subform is this:


Private Sub Text14_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmApplication"

If [AppID] = 0 Then
DoCmd.OpenForm stDocName, , acNewRec
Else
stLinkCriteria = "[AppID]=" & Me![AppID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

End Sub

I realise that at least the 'copy command' is missing but I haven't got a clue.

Thanks for your help,

Wally

 

[Mark Wild]

Wally,

If I understand ocrreclty what you're after, try adding this after your If statement try adding

forms!frmApplication!AppID=me!AppID

 

[Real Wally]

Hi Mark, sorry it took me so long to reply. I've been away for a few days.

Your suggestion doesn't work unfortunately. It makes sense it doesn't either as I want to open a new record for an Application and therefore there's no AppID to copy. The AppID is an autonumber.

I'll have another think about this. I'll probably have to come with a better description of the situation and will do so in a new post today.

 

[Numpty]

I have a something very similar within my database. It uses IsNull rather than the count of the ID.

Here is the code - try to adapt it for your application and it may work.

Private Sub cmdAccessFund_Click()
On Error GoTo Err_cmdAccessFund_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Access Fund Award"

stLinkCriteria = "[DelegateID]=" & Me![DelegateID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

' If no current Access Fund Record for delegate open blank form and transfer details
If IsNull([Forms]![Access Fund Award]![DelegateID]) Then
[Forms]![Access Fund Award]![DelegateID] = Me.DelegateID
Else

End If


Exit_cmdAccessFund_Click:
Exit Sub

Err_cmdAccessFund_Click:
MsgBox Err.Description
Resume Exit_cmdAccessFund_Click

End Sub

Works fine for me whereas if there is an Access Fund record it displays it otherwide creates a new record and transfers the DelegateID from the calling form.

 

[Real Wally]

Thanks Numpty.

It partially works now. When I click the button to go to the subform I now get a msgbox asking me for a parameter value. The value of the field that contains the link between the forms is displayed just above the empty field in the box. When I ignore the msgbox and click OK it opens the right form so I feel I'm nearly there.

Below is the code I now have. Any clue what may be the problem?

Private Sub OfficialActions_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllOfficialActions_PD"

stLinkCriteria = "[PriorityDocument]=" & Me![PriorityDocument]
DoCmd.OpenForm stDocName, , , stLinkCriteria
'If no current Official Action Record exists for PriorityDocument open blanc form and transfer details
If IsNull([Forms]![frmAllOfficialActions_PD]![PDNumber]) Then
[Forms]![frmAllOfficialActions_PD]![PriorityDocument] = Me.PriorityDocument
Else
End If

End Sub

Thanks,

wally

 

[Numpty]

To be honest Wally I'm really a novice myself at Access and I'm still learning so not too good at the old problem solving here. Perhaps someone else knows the problem.

The only thing I notice between the two sets of code is that mine passes the same number [DelegateID] between the forms as the 1st form is based on the Delegates Table so DelegateID is the primary key and the Access Fund form is based on the AccessFund table and has its own Primary Key [AccessFundID] and DelegateID is the foreign key.

Perhaps it is the way you have the tables/forms set up - does the form frmAllOfficialActions have its own underlying table with its own primary key? The PriorityDocument number can then be passed to that form and would be a link between the two then the Primary Key (PDNumber?) would be generated automatically.

Like I say I'm sure someone else can explain it better

 

[Real Wally]

To be honest Wally I'm really a novice myself at Access and I'm still learning

Well, so am I but that doesn't mean you cannot find solutions does it! I appreciate ALL serious input. It may not be the solution all the time but it does make you aware of other angles to look at the problem. Therefore, thanks for thinking with me.

[/QUOTE]Perhaps it is the way you have the tables/forms set up - does the form frmAllOfficialActions have its own underlying table with its own primary key?[/QUOTE]

Yes, it does.

[/QUOTE]The PriorityDocument number can then be passed to that form and would be a link between the two then the Primary Key (PDNumber?) would be generated automatically[/QUOTE].

Exactly what I was trying to do before but couldn't get to work. I was hoping to find a different route for the same solution here.

I may go back to the original idea, see if I can get a solution to that through this forum.

Thanks again,

Wally

 

[Numpty]

So would it not work if the code was...

If IsNull([Forms]![frmAllOfficialActions_PD]![PriorityDocument]) Then
[Forms]![frmAllOfficialActions_PD]![PriorityDocument] = Me.PriorityDocument

So PriorityDocument is the link and it creates its own PDNumber?

 

[Real Wally]

No, tried that some time ago.

It's got to be somthing simple that I'm just overlooking I think.