By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

New record gives error in "linked" form

P: n/a
Hi,

Hope you can help me on this...

I got 1 form with a sub form. On this subform there are only parts of
records, so you can select the one you want to edit.
When you dubbel click that record a second form will open and you see all
info of that record(also the info that was already shown on form 1)
Now is it posible to edit the record.

So far no problem, but

On the sub form on form1 you can also dubbel click the *(add new) record.
Form 2 will open with all blank fields.
After adding the new info, form2 dont want to close, becausse its missing a
linked ID to the main form1

The way I open form 2 is:

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Repair Query"

stLinkCriteria = "[RMA nr]=" & "'" & Me![RMA nr] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Form__DblClick:
Exit Sub

Err_Form__DblClick:
MsgBox Err.Description
Resume Exit_Form__DblClick

end sub

The main form1 is called [Monitor] with a auto ID called [Monitor ID]
The sub form is called [Repair Query Sub Form] with a linked [Rev Monitor
ID] to the main form [Monitor]
And the second form [Repair Query] " "
" " "

I also made a simple example database to show the problem:
http://groups.msn.com/MSAccessforum/...ssage&mview=1&
ID_Message=1362

Thanx
Xiphias
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Xiphias wrote:
The way I open form 2 is:

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Repair Query"

stLinkCriteria = "[RMA nr]=" & "'" & Me![RMA nr] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Try this instead. You RMA# is in the MainForm, not the subform.
Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

DoCmd.OpenForm "RepairQuery",,,,acFormAdd
Forms!RepairQuery![RMA nr] = Forms!YourMainFormName[rma nr]



Exit_Form__DblClick:
Exit Sub

Err_Form__DblClick:
MsgBox Err.Description
Resume Exit_Form__DblClick

end sub

The main form1 is called [Monitor] with a auto ID called [Monitor ID]
The sub form is called [Repair Query Sub Form] with a linked [Rev Monitor
ID] to the main form [Monitor]
And the second form [Repair Query] " "
" " "

I also made a simple example database to show the problem:
http://groups.msn.com/MSAccessforum/...ssage&mview=1&
ID_Message=1362

Thanx
Xiphias


Nov 12 '05 #2

P: n/a

"Salad" <oi*@vinegar.com> schreef in bericht
news:3F***************@vinegar.com...
Xiphias wrote:
The way I open form 2 is:

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Repair Query"

stLinkCriteria = "[RMA nr]=" & "'" & Me![RMA nr] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Try this instead. You RMA# is in the MainForm, not the subform.
Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

DoCmd.OpenForm "RepairQuery",,,,acFormAdd
Forms!RepairQuery![RMA nr] = Forms!YourMainFormName[rma nr]



Exit_Form__DblClick:
Exit Sub

Err_Form__DblClick:
MsgBox Err.Description
Resume Exit_Form__DblClick

end sub

The main form1 is called [Monitor] with a auto ID called [Monitor ID]
The sub form is called [Repair Query Sub Form] with a linked [Rev Monitor ID] to the main form [Monitor]
And the second form [Repair Query] " "
" " "

I also made a simple example database to show the problem:
http://groups.msn.com/MSAccessforum/...ssage&mview=1& ID_Message=1362

Thanx
Xiphias


Ok thanx I solved the problem like this:
----------------------------------------------------------
Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Repair Query"

stLinkCriteria = "[RMA nr]=" & "'" & Me![RMA nr] & "'"
If Me.NewRecord Then

DoCmd.OpenForm "Repair Query", , , , acFormAdd
Forms![Repair Query]![Rev Monitor ID] = [Forms]![Vrij goed]![Monitor Query
Subformulier1].Form![Monitor ID]

Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Form__DblClick:
Exit Sub

Err_Form__DblClick:
MsgBox Err.Description
Resume Exit_Form__DblClick

End Sub
--------------------------------------------------------
The only problem I have now is...

after opening a new record and closing it straight away, there will be an
new record with only the [Rev Monitor ID] and the rest is blank.
So I have to make a check if the [RMA nr] is empty, if so it has to ask if I
want to save this record Yes/No.

Do you know how to make this...?

Thanx again.
Xiphias


Nov 12 '05 #3

P: n/a
Xiphias wrote:
--------------------------------------------------------
The only problem I have now is...

after opening a new record and closing it straight away, there will be an
new record with only the [Rev Monitor ID] and the rest is blank.
So I have to make a check if the [RMA nr] is empty, if so it has to ask if I
want to save this record Yes/No.

Do you know how to make this...?

Thanx again.
Xiphias


Well, maybe not pass the ID. Do it when the record is saved. Since the ID may
not be required for viewing, in the Single form, you can make the ID in the
single form invisibible.

When the form (single) closes, you can check to see if is modified. In the
Forms BeforeUpdate event do something like
Sub Form_BeforeUpdate()
If Me.NewRecord Then
Me.YourIDFieldName = Forms!YourMainFormThatCalledThiswForm!ID
Endif
End Sub

The above would be done ONLY if the single form is ONLY called from the
form/subform. If you call the single form by itself somewhere else then you
need to check to see your main form is open (see syscmd()) and/or add some extra
verification checks that the ID field has not been filled in yet.

Nov 12 '05 #4

P: n/a
I hadn't the time to try it yet, but it sounds like it could be working.
I already had a "not so nice" idea to put a button with:

DoCmd.Close

Thats also solving the problem, but I dont understand why.
What's the differents between "DoCmd.Close" and pushing the "X" to close the
window.

Do you know if its possible to connect the"DoCmd.Close" commando to the "X",
that would make a perfect solution.

Tomorrow I will try your solition. I will let you know how it worked....

Thanx,
Xiphias

"Salad" <oi*@vinegar.com> schreef in bericht
news:3F***************@vinegar.com...
Xiphias wrote:
--------------------------------------------------------
The only problem I have now is...

after opening a new record and closing it straight away, there will be an new record with only the [Rev Monitor ID] and the rest is blank.
So I have to make a check if the [RMA nr] is empty, if so it has to ask if I want to save this record Yes/No.

Do you know how to make this...?

Thanx again.
Xiphias
Well, maybe not pass the ID. Do it when the record is saved. Since the

ID may not be required for viewing, in the Single form, you can make the ID in the single form invisibible.

When the form (single) closes, you can check to see if is modified. In the Forms BeforeUpdate event do something like
Sub Form_BeforeUpdate()
If Me.NewRecord Then
Me.YourIDFieldName = Forms!YourMainFormThatCalledThiswForm!ID
Endif
End Sub

The above would be done ONLY if the single form is ONLY called from the
form/subform. If you call the single form by itself somewhere else then you need to check to see your main form is open (see syscmd()) and/or add some extra verification checks that the ID field has not been filled in yet.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.