DoCmd.OpenForm and WhereCondition (1 Viewer)

Hi,

I have a form(frmMain) with no controls. All empty. Inside the form, i have a sub form(frmSub). I am trying to open from another form(frmCustomers) the frmMain... Easy part... The hard one is to open with a WhereCondition for the frmSub according to the previous form.

I want to doubleclik Memo and check if YesNoData is TRUE or FALSE.
If it is FALSE, open the frmMain and add 10new rows where CustomersID is frmCustomers.CustomersID.
If it is TRUE, simple open frmMain with WhereCondition CustomersID = me.CustomersID...

Forms:
frmMain = > No controls
frmSub = > SubID,CustomersID, Data1, Data2
frmCustomers => CustomersID, Memo, YesNoData

Code:
Private Sub CustomersID_DblClick(Cancel As Integer)
Dim stLinkCriteria As String
stLinkCriteria = "[Forms]![frmMain]![frmSub].Form.[CustomersID]=" & Me.CustomersID
If Me.YesNoData = FALSE Then
DoCmd.OpenForm "frmMain", , , , , Me!CustomersID
AddLines
Me.YesNo = "1"
'DoCmd.Close , "frmCustomers"
'DoCmd.Close , "frmMain"
'DoCmd.OpenForm "frmMain", , , stLinkCriteria
Else
'DoCmd.OpenForm "frmMain", , , stLinkCriteria
'DoCmd.Close , "frmCustomers"
End If
End Sub

Sub AddLines()
Dim i As Integer
Dim DefaultID As Long

DefaultID = Me.CustomersID
Forms!frmMain!frmSub.Form.AllowAdditions = True

For i = 1 To 10
DoCmd.GoToRecord , Forms!frmSubMain!frmSub.Form.CustomersID, acNewRec
Forms!frmMain!frmSub.Form.CustomersID= DefaultID
Next i

Forms!frmMain!frmSub.Form.AllowAdditions = False

End Sub



======================
I am getting an error 2489. I can't link to the sub form...
I test the code with not the frmMain... Simple frmSub and it was working great...
I added frmMain because i want to add later txt with sums from the frmSub...


Thank you very in advance.

:banghead::banghead::banghead:

Why open the form. Why don't you just add records at the table level using a recordset.

Thank you very much for your reply.

I don't know how to do it with recordset. I manage to it with i and next i and it works perfect. Simple and easy.

My problem is that i can't link to subform... CustomersID

I don't knw how to do it with recordset. I manage to it with i and next i and it works perfect. Simple and easy.

Click to expand...

Well, maybe not quite so simple or easy when you do it this way . What you are doing is also slow and inefficient. Take a look at this: http://www.functionx.com/vbaccess2003/howto/addrecord.htm

I don't know how to do it the way you are trying to do it. I have never tried it and never would, but maybe you could try passing CustomerID to the OpenArg of the Main form.

Here is my Test DB... In this one everything works fine...

Open the Form(tblTrimina) and you will see TriminaID, YesNo, Trimina...

Once you doubleClick Trimina it will check :
If YesNo is False and it will open Form(tblSunolo), add 10 new rows where TriminaID is the same from the first Form and close and reload with the Filter TriminaID.

If YesNo is True simple open the Form with Filter....

My problem is that i can't make it work if the second form is a sub form of an empty Main Form...

alpapak said:

My problem is that i can't make it work if the second form is a sub form of an empty Main Form...

Click to expand...

Yes, I know what the problem is and in my opinion the way to do this is by using a recordset, as I said in my earlier post.

I change the code and insert recordset...

Code:
Option Compare Database
Private Sub Trimina_DblClick(Cancel As Integer)
Dim stLinkCriteria As String
stLinkCriteria = "Forms!frmSubTriminaMain!frmSubTrimina.Form.TriminaID=" & Me.TriminaID

If Me.YesNo = 0 Then
AddLinesDAO
Me.YesNo = "1"
DoCmd.OpenForm "frmSubTriminaMain", , stLinkCriteria
Else
DoCmd.OpenForm "frmSubTriminaMain", , stLinkCriteria
End If
End Sub

Sub AddLinesDAO()
Dim dbTrimina As DAO.Database
Dim rstTrimina As DAO.Recordset
Dim i As Integer

Set dbTrimina = CurrentDb
Set rstTrimina = dbTrimina.OpenRecordset("tblSubTrimina")
For i = 1 To 10
rstTrimina.AddNew
rstTrimina("TriminaID").Value = Me.TriminaID
rstTrimina.Update
Next i
End Sub


The problem now, is still the same... I can open the FormMain(frmSubTriminaMain) but the sub form(frmSubTrimina) contains all the TriminaID's...

I need to open the form with a filter but it is not working...

Found it...

Pass openArg to new form
stLinkCriteria = Me.TriminaID

In Open Filter
Private Sub Form_Open(Cancel As Integer)
frmSubTrimina.Form.Filter = "[TriminaID]=" & Me.OpenArgs
frmSubTrimina.Form.FilterOn = True
End Sub