Weird SubForm behavior. (1 Viewer)

marimo · Jul 24, 2007

I am buffled. My problem is like so:

I have two forms. Form1 is a "dialog box" where a use specifies a date. OnClick opens Form2 with a criteria like this

DoCmd.OpenForm stDocName, , , stLinkCriteria

Form2 has a subform. Form2 has the following event Form_load(). The first run works fine. Form2 and its subform are populated. The second run does not populate the subform, only fields in the main form. If you make a change in the VBA code, even just putting a comment, it works again for one run and stop working for subsequent runs??? Please help.

Code:

Private Sub Form_Load()
    Dim db As Database
    Dim rs As Recordset
    SQL = "select * from purchase where [PURCHASEdate]=" & "#" & Forms![add - purchase dialog]![Date] & "#"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQL)
    'MsgBox SQL
    If rs.RecordCount > 0 Then
        rs.MoveLast
        rs.MoveFirst
        Me.PURCHASEroe = rs.Fields("PURCHASEroe")
        'MsgBox rs.Fields("PURCHASEroe")
    End If
    rs.Close
End Sub

Bilbo_Baggins_Esq · Jul 24, 2007

You use the words "second run", but does completing the "first run" and beginning the "second run" involve opening and closing the form?

It occurs to me that you have this set to run on Form_Load, but is the form is not loaded, it will not run on subsequent processing unless you add a close and a reopen for the form.

If this is the case, you could either:
add this to the finishing process for the record "run"

Code:

DoCmd.Close acForm, "Form2"
DoCmd.OpenForm "Form2", acNormal, "", "", , acNormal

Or you could add the same code for the Form_Load, to the finishing process for the record "run".

marimo · Jul 24, 2007

by "first run" I meant after modifying VB code, the form will run. I will then close this form and re-run it ("second run") without modifying VB code. It won't run. I have even close Access and relaunched it. It will still not run. Only after modifying VB code does it run only once ("first run"). I have a button on the form when I close this form using

DoCmd.Close acForm, "Form2"

It still won't work.

boblarson · Jul 24, 2007

Try these two things:

1. Change your first declarations to:

Code:

    Dim db As [color=red]DAO.[/color]Database
    Dim rs As [color=red]DAO.[/color]Recordset

2. After the rs.Close, put in

Code:

Set rs = Nothing
Set db = Nothing

Not sure if those will help, but they can't hurt and it's good to explicitly identify your DAO vs ADO code to disambiguate it for Access and also to do variable garbage collection to ensure the memory is cleared of the recordset object.

Bilbo_Baggins_Esq · Jul 24, 2007

hmmm, I've been playing around with this, but I keep getting a Type Mismatch on the line

Code:

    Set db = CurrentDb

I'm trying to work this into this code:

Code:

Private Sub MACHINE_NAME_COMBO_AfterUpdate()
Me.MACHINE_CATEGORY_COMBO.Requery
Me.MACHINE_NAME_COMBO = Me.MACHINE_NAME_INVIS
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim SQL As String
    
    SQL = "SELECT SheetDetail.SHEET_ID, SheetDetail.CAT_NAME, SheetDetail.PROB_NAME, SheetDetail.START_TIME, SheetDetail.END_TIME, SheetDetail.COMMENTS, SheetDetail.MACHINE_NAME, SheetDetail.MACHINE_CATEGORY FROM SheetDetail WHERE (((SheetDetail.CAT_NAME)='Paper' Or (SheetDetail.CAT_NAME)='Rewinder/Accumulator/Tailsealer' Or (SheetDetail.CAT_NAME)='Logsaw' Or (SheetDetail.CAT_NAME)='Admin/Scheduling'));"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQL)
    Me.MACHINE_NAME_INVIS = rs.Fields("MACHINE_NAME")
    rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

What an I doing wrong?

boblarson · Jul 25, 2007

Bilbo_Baggins_Esq said:
What an I doing wrong?

Change this:
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

to this
Dim db As DAO.Connection
Dim rs As DAO.Recordset

Bilbo_Baggins_Esq · Jul 25, 2007

Well, that's what I started with, but I get the error:
Can't find project or library on the line:

Code:

    Dim db As ADO.Connection

boblarson · Jul 25, 2007

Bilbo_Baggins_Esq said:
Well, that's what I started with, but I get the error:
Can't find project or library on the line:

Code:

Dim db As ADO.Connection

You would get that error for sure if you used
Dim db As ADO.Connection

that is D A O not A D O

Also, you will get the error too if you don't set a reference to DAO, if you are going to use DAO. I'm assuming you are going to use DAO (not ADO) due to the way you are opening the recordset: rs = db.OpenRecordset(SQL) which is DAO syntax.

Bilbo_Baggins_Esq · Jul 26, 2007

that is D A O not A D O

Also, you will get the error too if you don't set a reference to DAO,

DOH! spelling will get me all the time!

So, how do I set the reference to DAO?

And also, I keep seeing this Me. in a lot of different code here?
Would you mind cluing me into what that is?
The code above was actually copied from some other code to use as an example for this DAO connection, but even it has the Me. prefix.

so could you help me out a little bit with setting the DAO connection, and an explanation of the Me. ?

boblarson · Jul 26, 2007

You set the reference to DAO in Tools > References and then find Microsoft DAO 3.x (x being a number).

The ME keyword is used, as a shortcut, to refer to the current form or report where you are coding. It also lets you use Intellisense so that you can type Me and a period and then you get a list of the properties and methods of that form or report.

Bilbo_Baggins_Esq · Jul 26, 2007

DOH!
I knew that...
I was just testing you...
8^)

Thanks for the tip on the ME keyword!

Weird SubForm behavior. (1 Viewer)

marimo

New member

Bilbo_Baggins_Esq

Registered User.

marimo

New member

boblarson

Smeghead

Bilbo_Baggins_Esq

Registered User.

boblarson

Smeghead

Bilbo_Baggins_Esq

Registered User.

boblarson

Smeghead

Bilbo_Baggins_Esq

Registered User.

boblarson

Smeghead

Bilbo_Baggins_Esq

Registered User.

Similar threads

Users who are viewing this thread