asking assistance again on continuous form (1 Viewer)

wind20mph · Dec 20, 2014

I have been searching for examples from all searches but i could not find what i was looking for.

i found a few regarding continuous form data but its not the sample that fits.

attached here is my screen shot and my accdb file (MSAccess 2013)

all i need help is how do they populate unbound continuous form from msaccess connecting to mysql server.

it should display 5 data but in my case it does show only one data.

thanks in advance

essaytee · Dec 20, 2014

Does this help, MySQL doc page relating to connecting to MySQL from Access.

Steve.

wind20mph · Dec 20, 2014

essaytee said:
Does this help, MySQL doc page relating to connecting to MySQL from Access.

Steve.

it did helped a lot during previous year(2013) only regarding the connection string. but, i would like to do a faster way, because if the table is linked, the process of running the front-end (ms access) is very slow (especially to wireless networks) but with just a connection string, it is very fast and efficient. however, my problem is the continuous form, it does not give a record preview. on the other hand, if i use a single form with navigation, it gives everything and runs very fast even at remote DSL operated sites.

essaytee · Dec 20, 2014

wind20mph said:
it did helped a lot during previous year(2013) only regarding the connection string. but, i would like to do a faster way, because if the table is linked, the process of running the front-end (ms access) is very slow (especially to wireless networks) but with just a connection string, it is very fast and efficient. however, my problem is the continuous form, it does not give a record preview. on the other hand, if i use a single form with navigation, it gives everything and runs very fast even at remote DSL operated sites.

I haven't got MySQL installed so I can't perform some simple tests. Time for others to chime in.

Steve.

JHB · Dec 20, 2014

wind20mph said:
I have been searching for examples from all searches but i could not find what i was looking for.

i found a few regarding continuous form data but its not the sample that fits.
..
all i need help is how do they populate unbound continuous form from msaccess connecting to mysql server.

Either you base the form's data on a query or you create a table, (tempoary) or set the form's recordset to the rsE recordset.
Look in the Help file for some sample code - keyword Form.Recordset

Code:

Set Me.Recordset = rsE

wind20mph said:
it should display 5 data but in my case it does show only one data.

I think you mean 5 sets of data, (equal 5 rows)!

wind20mph · Dec 22, 2014

thanks JHB, you got my problem. but in my case, still it does not appear to be as intended. it should display 5 rows, but only one row is appearing, the last record(not the first record).

i have tried a couple of ways, like, linking the odbc (it does appear), but i prefer not to have a linked table or odbc because it consumes the loading transition and slows down the system. i tried the set me.form.recordset = rse but it has another error cancelling the form load. the error was: (Run-time error '3265' Item cannot be found in the collection corresponding to the requested name or ordinal). Another error is if i put the set me.form.recordset = rsE after the rsE.Open strE,conn, adOpenStatic,adLockOptimistic this error appears ( Data provider or other service returned an E_Fail status)

but i can see some access programmers who made it possible and they placed a vba password so i cannot view the codes.

wind20mph · Dec 22, 2014

ok, i can seem to analyze how it does worked. i have replaced the microsoft activex data object 2.1 to 6.1 in the references.

and here is the trial code:

Code:

Private Sub Form_Load()
Dim myDB As ADODB.Connection
Dim rsE As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT eid,ename,dp FROM eqpt WHERE eqptype='Service Vehicle'"
Set myDB = New ADODB.Connection
myDB.CursorLocation = adUseClient
myDB.ConnectionString = connHBK
myDB.Open

Set rsE = New ADODB.Recordset
rsE.Open strSQL, myDB, adOpenStatic, adLockOptimistic


Dim varCnt As Integer

varCnt = rsE.RecordCount
Set Me.Form.Recordset = rsE

If varCnt > 0 Then
    rsE.MoveLast
    rsE.MoveFirst
    Do While Not rsE.EOF
        Me![Text0].Value = rsE("eid")
        Me![Text2].Value = rsE("ename")
        Me![Text4].Value = rsE("dp")
        MsgBox rsE("eid")
        rsE.MoveNext
    Loop
End If
End Sub

the msgbox is to check which equipment id was checked.

and the real problem was the loop. it placed all values to the control (text0 to text4) whichever the last record is. so populating the subform is really my issue here, not the codes.

how do i make the subform move to the next set of controls after placing the value of from the adodb.recordset?

anyone who have a nice heart who can share how this works. please post your reply.

i am not a computer geek or any computer course, so i rely mostly in this forum.

thanks in advance

Galaxiom · Dec 22, 2014

wind20mph said:
how do i make the subform move to the next set of controls after placing the value of from the adodb.recordset

Setting the recordset as the Form's recordet should be sufficient. Looping through the form itself would be a very clumsy thing and would not work anyway.

I have successfully used similar code to return an ADODB recordset from a Stored Procedure on MS SQL Server 2005 with the same settings as you have used. My connection was OLEDB but I doubt this would matter.

As you are having problems with the subform I would suspect you may be trying to use Master and Child Link Fields. I very much doubt that would be supported other than via linked tables.

Instead use the OnCurrent Event to feed the criteria used to create the recordset. The best way to do this is with a Stored Procedure. When I implimented this in one of my apps, the subform performance went from several seconds (using ODBC linked tables and Link Fields) to almost instant. (This is coming from a table with over forty million records.)

However using ADODB recordsets with forms can be notoriously frustrating. It may be that MySQL doesn't support it so well or there is some setting change required. I have not used MySQL so I don't know.

wind20mph · Dec 22, 2014

thanks for the reply Galaxuom!, it was quite an enlightenment. i do use MySQL connecting via 3.5 ODBC driver. it might be frustrating to find difficulties in populating subform. anyhow, the bright side was just in the code was in the condition. i have tried several patterns and got it correctly.

i use the msgbox to track where i went wrong and it was not even in the loop. the problem was in the conditions that generate -1 record or .EOF. so this solves all the predicament and the stupidity i have cause.

thanks for the enlightenment, i have completed the target issue. everything run as intended.

for those who wish to know the correct procedure, here it is:

Code:

Private Sub Form_Load()
        Dim myDB As ADODB.Connection
        Dim rsE As ADODB.Recordset
        Dim strEQ As String
If IsLoaded("fEMain") Then
    Forms![fEMain].Visible = False
    If Forms![fEMain]![lblTicker].Caption = "A" Then
        
        
        strEQ = "SELECT eid,ename,snpln,dp,location,acost,currentuser,eqptype FROM eqpt WHERE eqptype='Service Vehicle'"
        
        Set myDB = New ADODB.Connection
        myDB.CursorLocation = adUseClient
        myDB.ConnectionString = connHBK
        myDB.Open
        
        Set rsE = New ADODB.Recordset
        rsE.Open strEQ, myDB, adOpenStatic, adLockOptimistic
        
        Set Me.Recordset = rsE
        
        If rsE.RecordCount > 0 Then
            rsE.MoveFirst
            While Not rsE.EOF
            Me![eid].Value = rsE("eid")
            Me![ename].Value = rsE("ename")
            Me![snpln].Value = rsE("snpln")
            Me![dp].Value = rsE("dp")
            Me![location].Value = rsE("location")
            Me![acost].Value = rsE("acost")
            Me![currentuser].Value = rsE("currentuser")
            rsE.MoveNext
            Wend
        End If
        
        
        rsE.Close
        Set rsE = Nothing
        myDB.Close
        Set myDB = Nothing
        
        Me![Label18].Caption = "Plate No"
    ElseIf Forms![fEMain]![lblTicker].Caption = "B" Then
        'Dim myDB As ADODB.Connection
        'Dim rsE As ADODB.Recordset
        'Dim strEQ As String
        
        strEQ = "SELECT eid,ename,snpln,dp,location,acost,currentuser,eqptype FROM eqpt WHERE eqptype='Bldg Equipment'"
        
        Set myDB = New ADODB.Connection
        myDB.CursorLocation = adUseClient
        myDB.ConnectionString = connHBK
        myDB.Open
        
        Set rsE = New ADODB.Recordset
        rsE.Open strEQ, myDB, adOpenStatic, adLockOptimistic
        
        Set Me.Recordset = rsE
        
        If rsE.RecordCount > 0 Then
            rsE.MoveFirst
            While Not rsE.EOF
            Me![eid].Value = rsE("eid")
            Me![ename].Value = rsE("ename")
            Me![snpln].Value = rsE("snpln")
            Me![dp].Value = rsE("dp")
            Me![location].Value = rsE("location")
            Me![acost].Value = rsE("acost")
            Me![currentuser].Value = rsE("currentuser")
            rsE.MoveNext
            Wend
        End If
        
        
        rsE.Close
        Set rsE = Nothing
        myDB.Close
        Set myDB = Nothing
        Me![Label18].Caption = "Serial No"
        
    ElseIf Forms![fEMain]![lblTicker].Caption = "C" Then
        'Dim myDB As ADODB.Connection
        'Dim rsE As ADODB.Recordset
        'Dim strEQ As String
        
        strEQ = "SELECT eid,ename,snpln,dp,location,acost,currentuser,eqptype FROM eqpt WHERE eqptype='Computer'"
        
        Set myDB = New ADODB.Connection
        myDB.CursorLocation = adUseClient
        myDB.ConnectionString = connHBK
        myDB.Open
        
        Set rsE = New ADODB.Recordset
        rsE.Open strEQ, myDB, adOpenStatic, adLockOptimistic
        
        Set Me.Recordset = rsE
        
        If rsE.RecordCount > 0 Then
            rsE.MoveFirst
            While Not rsE.EOF
            Me![eid].Value = rsE("eid")
            Me![ename].Value = rsE("ename")
            Me![snpln].Value = rsE("snpln")
            Me![dp].Value = rsE("dp")
            Me![location].Value = rsE("location")
            Me![acost].Value = rsE("acost")
            Me![currentuser].Value = rsE("currentuser")
            rsE.MoveNext
            Wend
        End If
        
        
        rsE.Close
        Set rsE = Nothing
        myDB.Close
        Set myDB = Nothing
        
        Me![Label18].Caption = "Serial No"
    End If
    
End If
End Sub

Complete and concise.

asking assistance again on continuous form (1 Viewer)

wind20mph

MS Access User Since 1996

Attachments

essaytee

Need a good one-liner.

wind20mph

MS Access User Since 1996

essaytee

Need a good one-liner.

JHB

Have been here a while

wind20mph

MS Access User Since 1996

wind20mph

MS Access User Since 1996

Galaxiom

Super Moderator

wind20mph

MS Access User Since 1996

Similar threads

Users who are viewing this thread