[RESOLVED]BOF and EOF [/RESOLVED]

[Beengie]

Well...
it's all mendhak's fault

I changed over my ADO object to the code...

I having a problem with EOF and BOF. When I reach BOF or EOF I get this error:
Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
Here is my code:

Public Sub Form_Load()
 
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & App.Path & "\bb-calc.mdb"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * from tblBrushBed", cn, adOpenDynamic, adLockPessimistic
    FillFields
End Sub
 
Private Sub cmdPrev_Click()
    If rs.BOF = False Then
        rs.MovePrevious
        FillFields
    End If    
End Sub
 
Private Sub cmdNext_Click()
    If rs.EOF = False Then
        rs.MoveNext
        FillFields
    End If
End Sub

[mendhak]

Is the table in the database empty, by any chance?

[Beengie]

No, it is not empty.
I have 3 records in the database.
It only happens when I try to pass the existing records
Do I need to do a recordcount, or open the table a different way to make it work?

[mendhak]

Hmm... Which line gets highlighted?

[mendhak]

Try this:

Public Sub Form_Load()
 
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & App.Path & "\bb-calc.mdb"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * from tblBrushBed", cn, adOpenDynamic, adLockPessimistic
    FillFields
End Sub
 
Private Sub cmdPrev_Click()
rs.MovePrevious
 
    If rs.BOF Then
        rs.MoveFirst
    End If    
fillfields
End Sub
 
Private Sub cmdNext_Click()
rs.MoveNext
    If rs.EOF  Then
        rs.MoveLast
    End If
FillFields
End Sub

[randem]

Beengie


What does FillFields do? Post Code?

[Beengie]

It highlights the first line below in the fill fields sub

Public Sub FillFields()
    txtCstmr.Text = rs.Fields("dbCstmr")
    txtDate.Text = rs.Fields("dbDt")
    txtDueDate.Text = rs.Fields("dbDDt")
    txtNPns.Text = rs.Fields("dbNPns")
    txtBrshWdth.Text = rs.Fields("dbBrshWdth")
    txtElev.Text = rs.Fields("dbElvtn")
    txtNDrvs.Text = rs.Fields("dbNDrvs")
    txtFrmSlctn.Text = rs.Fields("dbFrm")
    txtBrngSlctn.Text = rs.Fields("dbBrngs")
    txtChnSlctn.Text = rs.Fields("dbChn")
    txtSprcktSlctn.Text = rs.Fields("dbSprckts")
    txtClntChnSlctn.Text = rs.Fields("dbClntChn")
    txtMDSlctn.Text = rs.Fields("dbMnDrvHP")
    txtGrmtrSlctn.Text = rs.Fields("dbGrMtrBrnd")
    txtHrdwrSlctn.Text = rs.Fields("dbHrdwr")
End Sub

I tried the example above, got the same errors...

[mendhak]

Can you post the form with the MDB file?

Your code seems fine.

(Yeah yeah, all my fault)

[Beengie]

maybe you can help with other stuff in there also...
like structure

* edited *

[mendhak]

Dude... you didn't try the code I gave you!

Private Sub cmdPrev_Click()
rs.MovePrevious
 
    If rs.BOF Then
        rs.MoveFirst
    End If
    FillFields
End Sub
 
 
Private Sub cmdNext_Click()
rs.MoveNext
 
    If rs.EOF Then
        rs.MoveLast
    End If
    FillFields
End Sub
 
'Oh, and you forgot...
 
Private Sub cmdClose_Click()
Unload Me
 
End Sub

It worked fine after i made the above modifications

[mendhak]

*edit*

[Beengie]

Originally quoted by Beengie
I tried the example above, got the same errors...

I thought that I put that in...
I don't know what I did wrong, I tried it again and it worked.
Thanks

do you have any suggestions for my code?

this is the first application that I have made with this many calcualtions and variables. Can I be more efficient? Should I do things different?

[mendhak]

Originally posted by Beengie
Can I be more efficient? Should I do things different?

Yeah, lot of variables in there. What I'll suggest will be mostly my opinion, so you don't really have to follow it:

Select Case txtFrmSlctn.Text
        Case "Stainless Steel": T3_4x7_16PFt = 3.2: T3_4X7_16Mtl = txtFrmSlctn.Text ' same as above
        Case "Epoxy": T3_4x7_16PFt = 3.2: T3_4X7_16Mtl = "HR Steel" ' same as above
    End Select

Could be

Select Case txtFrmSlctn.Text
 
        Case "Stainless Steel"
         T3_4x7_16PFt = 3.2
         T3_4X7_16Mtl = txtFrmSlctn.Text ' same as above
 
       Case "Epoxy"
       T3_4x7_16PFt = 3.2
        T3_4X7_16Mtl = "HR Steel" ' same as above
 
    End Select

This is obviously the same thing, but would be for readability, so you know in case some miscalcuations are occuring or erroneous values are being assigned.

However, looking at the entire form, doing that would require a LOT of changes in there...



One more thing:

Select Case nValue
        Case "CrossHQty"
            sngTmpQty = intNSup / 2
            CrossH = sngTmpQty
        Case "CrossHLen"
            sngTmpLngth = (sngFtGdWd * 2) + txtBrshWdth.Text
            CrossH = sngTmpLngth & """"
        Case "CrossHSize"
            CrossH = SQT3x3_16Sz
        Case "CrossHMat"
            CrossH = SQT3x3_16Mtl
        Case "CrossHPFt"
            CrossH = FormatCurrency(SQT3x3_16PFt, 2, 0, 0, -1)
        Case "CrossHMTtl"
            curMkpSnd = sngTmpQty * sngTmpLngth * (SQT3x3_16PFt / 12)
            CrossH = FormatCurrency(curMkpSnd, 2, 0, 0, -1)
            Call fncAdd(curMkpSnd)
        Case "CrossHMkp"
            Call fncMkpMtl(curMkpSnd)
            CrossH = FormatCurrency(curMkpSndT, 2, 0, 0, -1)
    End Select

You could have a Case Else in there, for incorrect values, so that you know some incorrect value has been assigned, and you can deal with it in some way.

[Beengie]

I need to finish the data connection buttons (update, add, delete). I haven't converted that yet (sick all weekend). I am planning on taking those hard coded variables out and putting them into a few separate tables for later upating.

It got carried away when I started copy/pasting and changing variable names. Now you see the reason I wanted to understand my data connection. After further reveiw, I find the dataconnection strings are identical to my uses in ASP. Thank God
Got any suggestions for add, update, and delete?

Originally quoted by mendhak
*edit*

Is that edit or ribut?

[mendhak]

For Update, you can simply reverse this:

txtCstmr.Text = rs.Fields("dbCstmr")
    txtDate.Text = rs.Fields("dbDt")
    txtDueDate.Text = rs.Fields("dbDDt")
    txtNPns.Text = rs.Fields("dbNPns")
    txtBrshWdth.Text = rs.Fields("dbBrshWdth")
    txtElev.Text = rs.Fields("dbElvtn")
    txtNDrvs.Text = rs.Fields("dbNDrvs")
    txtFrmSlctn.Text = rs.Fields("dbFrm")
    txtBrngSlctn.Text = rs.Fields("dbBrngs")
    txtChnSlctn.Text = rs.Fields("dbChn")
    txtSprcktSlctn.Text = rs.Fields("dbSprckts")
    txtClntChnSlctn.Text = rs.Fields("dbClntChn")
    txtMDSlctn.Text = rs.Fields("dbMnDrvHP")
    txtGrmtrSlctn.Text = rs.Fields("dbGrMtrBrnd")
    txtHrdwrSlctn.Text = rs.Fields("dbHrdwr")

to

Private Sub cmdUpdate_Click()
 
rs.Fields("dbCstmr") = txtCstmr.Text
rs.Fields("dbDt") = txtDate.Text
rs.Fields("dbDDt") = txtDueDate.Text
' etc, etc
 
'after assigning it all, then do an rs.update
rs.Update
 
End Sub

OR you could go the more efficient way and

Private Sub cmdUpdate_Click()
 
Dim strsqlupdate As String
strsqlupdate = "UPDATE tablename SET dbCstmr = '" & txtCstmr.Text & "', dbDt = '" & txtDate.Text & "', dbDDt = '" & txtDueDate.Text & ", and so on and so forth;"
 
cn.Execute strsqlupdate
'then close and reopen the recordset, and refill the form
 
End Sub

[mendhak]

Looking at the form, I *think* that adding directly to the form would be a little confusing for the user. It is to me at least because I can't understand what I want with a "scrubber" and "squeegee". I would suggest a new smaller form open up, and getting the user to enter values there. These values can be saved to a public variable in the original form

After that,

Private Sub cmdAdd_Click()
 
Dim strsqladd As String
strsqladd = "INSERT INTO tablename(field1, field2, field3....) VALUES('" & txtCstmr.Text & "','" & txtDate.Text & "','" & txtDueDate.Text & "',............................"
cn.Execute strsqladd
'then close and reopen the recordset, and refill the form
 
End Sub

Deleting would be easier. I'm assuming you have a primary key in your table at least.

strsqldelete = "DELETE FROM tablename WHERE primarykeyfield = '" & objrs.fields("primarykeyfield") & "';"
cn.Execute strsqldelete
'then close and reopen the recordset, and refill the form

HTH

[Beengie]

ok, couple of things...

'then close and reopen the recordset, and refill the form

open it back up to the current record?
I was only using the textboxes under the options for me to see if the option group code was working for me (I am going to remove them). Would you look at the option group function?

Public Sub CtrlStOptn(ctrl As Control, TagID As String)
Dim Cntrl
    On Error Resume Next
    
    For Each Cntrl In ctrl.Parent
        With Cntrl
            If .hwnd <> ctrl.hwnd And .Tag = TagID Then .Value = False
        End With
    Next Cntrl
End Sub
 
Private Sub optFrmEpxy_Click()
    If optFrmEpxy.Value = True Then CtrlStOptn optFrmEpxy, optFrmEpxy.Tag
    txtFrmSlctn.Text = "Epoxy"
End Sub
 
Private Sub optFrmSS_Click()
    If optFrmSS.Value = True Then CtrlStOptn optFrmSS, optFrmSS.Tag
    txtFrmSlctn.Text = "Stainless Steel"
End Sub

Is the CtrlStOptn necessary?

[mendhak]

Originally posted by Beengie

open it back up to the current record?

After a refresh and update, yeah. You can save the bookmark to a variable, and move to that bookmark value after the update/refresh is complete.

For adding, you can move to the newest record, considering you know in which place it will apear in the browsing order on the form.

For delete, move to bookmark minus 1.

I was only using the textboxes under the options for me to see if the option group code was working for me (I am going to remove them). Would you look at the option group function?

Public Sub CtrlStOptn(ctrl As Control, TagID As String)
Dim Cntrl
    On Error Resume Next
    
    For Each Cntrl In ctrl.Parent
        With Cntrl
            If .hwnd <> ctrl.hwnd And .Tag = TagID Then .Value = False
        End With
    Next Cntrl
End Sub
 
Private Sub optFrmEpxy_Click()
    If optFrmEpxy.Value = True Then CtrlStOptn optFrmEpxy, optFrmEpxy.Tag
    txtFrmSlctn.Text = "Epoxy"
End Sub
 
Private Sub optFrmSS_Click()
    If optFrmSS.Value = True Then CtrlStOptn optFrmSS, optFrmSS.Tag
    txtFrmSlctn.Text = "Stainless Steel"
End Sub

Is the CtrlStOptn necessary?

What are you doing here? Don't you think it's a little inefficient that CtrlStOptn runs about 50 times each time I click a radio button?

All you need in the frames are your option boxes, and the option box will get selected "as you click"

Tell me if your CtrlStOptn accomplishes something else!

[Beengie]

I looked for some optiongroup codes on planet source code, and that is what I found. I believe that is what I would need if I didn't use an option frame for the buttons.

what's the best way for vbYesNo handling?

btw...

Thanksforallyouhelp!!

[mendhak]

Like this:

Dim lret As Long
lret = MsgBox("Are you sure?", vbYesNo, "CONFIRMATION")
 
If lret = vbYes Then
'do something
'delete the ****er!
 
Else
'do something else
End If

HTH

[Beengie]

having problems with the delete

Private Sub cmdDelete_Click()
    Dim strsqldelete As String
    Select Case MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Delete Record")
    Case vbYes
    strsqldelete = "DELETE FROM tblBrushBed WHERE intbbID = '" & rs.Fields("bbID") & "';"
    cn.Execute strsqldelete
    rs.Close
    rs.Open "SELECT * from tblBrushBed", cn, adOpenDynamic, adLockPessimistic
    FillFields
    Case vbNo
    'nothing to do
   End Select
    
End Sub

I declared the variable intbbID as an integer in the form.
It highlights cn.Execute strsqldelete
PS. I like the frog better

[mendhak]

Private Sub cmdDelete_Click()
    Dim strsqldelete As String
    Select Case MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Delete Record")
    Case vbYes
    strsqldelete = "DELETE FROM tblBrushBed WHERE intbbID = " & rs.Fields("bbID") & ";"
    cn.Execute strsqldelete
    rs.Close
    rs.Open "SELECT * from tblBrushBed", cn, adOpenDynamic, adLockPessimistic
    FillFields
    Case vbNo
    'nothing to do
   End Select
    
End Sub

Try that.

The reason you were having a problem is because (my guess) that intbbID is a numeric field. Whenever putting variables into numeric fields, you need not have the single quote (') in there. For textual fields, you should have it in there.

[mendhak]

Originally posted by Beengie

PS. I like the frog better

Me too, but my PR agent told me to go for a temporary makeover in order to retain smoker-clients.

[Beengie]

same problem...
I even tried delaring the intbbID to strbbID as a string...
I put the single quotes back in for that...

[mendhak]

I just checked your database.

It's bbID not, intbbID

Private Sub cmdDelete_Click()
    Dim strsqldelete As String
    Select Case MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Delete Record")
    Case vbYes
    strsqldelete = "DELETE FROM tblBrushBed WHERE bbID = " & rs.Fields("bbID") & ";"
    cn.Execute strsqldelete
    rs.Close
    rs.Open "SELECT * from tblBrushBed", cn, adOpenDynamic, adLockPessimistic
    FillFields
    Case vbNo
    'nothing to do
   End Select
    
End Sub

*applies more makeup*

[Beengie]

ahh, I see...

I needed to switch the code you posted from rs.Fields("bbID") to intbbID.

I am tired and going to turn in for the evening.

I sure appreciate the help

[mendhak]

No probs.