Sql Help - Needed Bad!!!!

I thought that I had a grip on this project, but I am not too sure now...

This one form I created was supposed to show me data from an Access query by way of a Recordset...what happens though is that it shows the data but will not for the life of me move to the next record...

I have attempted to attach a .zip file of a scaled down project with the form in question (frmCB.Form)

If I am successful in attaching this (which I have not yet!), then you will need to make one adjustment...I attached the Access database, that will need to be placed on a seperate drive other than the floppy, it does not seem to work well otherwise...

Also, when you get to the Login screen, type ' b452345 '...thank you..

If the attachment does not work, would someone allow me to email them the project?

This attachment business is for the birds!!!!:mad:

the zip file I have is only 67,000 bytes...and I am supposed to be able to send up to 105, 360 bytes!!!

This is ridiculous!

Anywho...

If someone can assist me with this I would be very grateful!

just post the part of the code that your having the problem with.

Ok here is what I have:

VB Code:

Private Sub chkCB_Click()
                 'the code below I believe is where the problem lies
         
Private Sub cmdNextRec_Click()
Const Formclose As Long = 3021
On Error GoTo ErrorMsg
 
Dim msgbtn As Integer, strSelect As String, msgbtn2 As Integer
Set rs = New Recordset
 
strSelect = "SELECT * FROM qryCompletedRef WHERE CallBackShift = '" & txtSalesShift.Text & "'"
rs.Open strSelect, MyDataEnvironment.MyConnection, adOpenDynamic, adLockOptimistic
 
If txtSaleRep.Text = "" Then
MsgBox "Please enter your name in the appropriate box before continuing!", vbCritical, "Your Name?"
txtSaleRep.SetFocus
Exit Sub
Else
If lstAppGen.Text = "" Then
MsgBox "Please select if an application was generated!", vbCritical, "Application Generated?"
lstAppGen.SetFocus
Exit Sub
Else
chkCB.Value = 0
rs.Fields("AgentCmpltd") = txtSaleRep.Text
rs.Fields("CmpltdDate") = txtDateCmpltd.Text
rs.Fields("AppGen") = lstAppGen.Text
txtSlsRefNot.Text = rs.RecordCount
txtSlsRefNot.Text = rs.RecordCount - 1
   'when I click on this command button it changes the record count
  ' as if it was updating the query, but the query does not show the updated fields such as "AgentCmpltd"
rs.MoveNext
End If
End If
    
If rs.EOF = True Then
msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
End If
 
If msgbtn = vbOK Then
msgbtn2 = MsgBox("Would you like to quit the program now?", vbInformation + vbYesNo, "Quit?")
If msgbtn2 = vbYes Then
rs.Close
End
 
Else
'do nothing
End If
End If
 
DisplayEnd:
    Exit Sub
 
ErrorMsg:
    If Err = Formclose Then
    frmMsgboxLstRec.Show
   End If
Resume DisplayEnd
End Sub
 
Private Sub Form_Activate()
Dim msgbtn As Integer, strSelect As String
Set rs = New Recordset
 
strSelect = "SELECT * FROM qryCompletedRef WHERE CallBackShift = '" & txtSalesShift.Text & "'"
rs.Open strSelect, MyDataEnvironment.MyConnection, adOpenDynamic, adLockOptimistic
 
 
If rs.RecordCount = 0 Then
msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
Else
    rs.MoveFirst
    txtDateRef.Text = rs.Fields("DateRef")
    txtCstmrFName.Text = rs.Fields("CustomerFName")
    txtCstmrLName.Text = rs.Fields("CustomerLName")
    txtSSNum.Text = rs.Fields("SS")
    txtHSBCAcct.Text = rs.Fields("ExsistAcct")
    If txtBrochOnly.Text = "" Or Null Then
        'do nothing
    Else
        txtBrochOnly.Text = rs.Fields("BrochOnly")
    End If
    If txtBrochAddress.Text = "" Or Null Then
        'do nothing
    Else
        txtBrochAddress.Text = rs.Fields("BrochAddress")
    End If
    txtCBPref.Text = rs.Fields("CallbackDate")
    txtBestTime.Text = rs.Fields("BestTime")
    txtCBShift.Text = rs.Fields("CallBackShift")
    txtPriPhn.Text = rs.Fields("PrimaryPhone")
    
        If txtCallBackCmmnts.Text = "" Or Null Then
            'do nothing
        Else
            txtCallBackCmmnts.Text = rs.Fields("CallbackComments")
        End If
        If txtSecPhn.Text = "" Or Null Then
            'do nothing
        Else
            txtSecPhn.Text = rs.Fields("SecPhone")
        End If
        If txtChecking.Text = "" Or Null Then
            'do nothing
        Else
            txtChecking.Text = rs.Fields("CHECKING")
        End If
        If txtSavings.Text = "" Or Null Then
            'do nothing
        Else
            txtSavings.Text = rs.Fields("SAVINGS")
        End If
        If txtCredit.Text = "" Or Null Then
            'do nothing
        Else
            txtCredit.Text = rs.Fields("CREDIT CARD")
        End If
        If txtATM.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtATM.Text = rs.Fields("ATM/MASTERMONEY")
        End If
        If txtSelect.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtSelect.Text = rs.Fields("SELECT CREDIT")
        End If
        If txtInsurance.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtInsurance.Text = rs.Fields("INSURANCE")
        End If
        If txtInvestments.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtInvestments.Text = rs.Fields("INVESTMENTS")
        End If
        If txtCD.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtCD.Text = rs.Fields("CD")
        End If
        If txtHomeEquity.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtHomeEquity.Text = rs.Fields("HOME EQUITY")
        End If
        If txtInstallment.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtInstallment.Text = rs.Fields("INSTALLMENT")
        End If
        If txtTuition.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtTuition.Text = rs.Fields("TUITION PLUS LOC")
        End If
        If txtOther.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtOther.Text = rs.Fields("OTHER")
        End If
        If txtFinalCmmnts.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtFinalCmmnts.Text = rs.Fields("FinalComments")
        End If
                   
txtSlsRefNot.Text = rs.RecordCount
End If
 
If msgbtn = vbOK Then
Unload Me
 
End If
 
 
rs.Close
 
End Sub

As I stated I can not seem to get the recordset to move to the next record, and for that matter it is also not updating in the underlying query...

What is both strange & interesting is that when I do select the cmdNextRec button, it changes the recordcount of the underlying query as if something happened...

Why I say that is strange is the query has criteria set up to sift out records from the Main table that do not meet certain criteria...

So when this Call Back form is activated there is a textbox on the form that shows how many records are currently in the query...this is the same textbox that becomes adjusted when I click on Next...

But the fields remain the same...and when I close the form and look at the Access query, nothing has changed!!

I just thought that might help you to help me....

Thank you!

While await any assistance on this important matter...I have been trying to test oout this problem on a demo form...and it all comes down to the fact that I cann not seem to get the recordset to move to the next record by using the MoveNext function....


Can someone please show me how this works...

:confused: :confused:

Uh, I think you're missing an rs.Update call to update the recordset once you've changed it. I think that without this, the database does a rollback.

Destined

With or without the rs.Update ...the recordset is till not moving to the next record!

It has to have something to do with the rs.MoveNext...no idea why it is doing this?????

All these kind of line :

VB Code:

If txtOther.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtOther.Text = rs.Fields("OTHER")
        End If

should be change to :

VB Code:

If txtOther.Text <> "" Or txtOther.Text <> Null Then
            txtOther.Text = rs.Fields("OTHER")
        End If

You cannot just put Or Null you need a condition

I appreciate that info, but that still does not solve this problem with the inability to move through the recordset....

Also the fields were working fine with filling the fields with the appropriate data from the query...

ZIP your project, only put the relevant files in the folder...reduce the db size and e-mail me the project at: [email protected]

I was looking at your code earlier this morning, and I think that you're having logic errors, not that MoveNext isn't working.

From what I remember looking at your code, you perform your query the same every time you click NextRecord. You automatically fill the textboxes, with the first value but never any other values? This is simply because you're not moving to the next record BEFORE you display the text.

VB Code:

Dim recordToDisplay As Integer
 
Private Sub Command1_Click()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim counter As Integer
    
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open "parts.mdb"
    
    rs.Open "SELECT * FROM Parts", conn
    
    ' since we clicked on moveNext, increment recordIndex
    recordToDisplay = recordToDisplay + 1      
    
    ' ---- THIS IS THE PART YOU WANT!! -----
    ' Now move to the record we want
    counter = 0
    While counter < recordToDisplay And Not rs.EOF
        counter = counter + 1
        rs.MoveNext
    Wend
    
    If rs.EOF Then
        MsgBox "cannot go past last record"
        recordToDisplay = recordToDisplay - 1       ' so our current record isn't past EOF
        GoTo CleanUp
    End If
    
    txtData = rs!Cost
    
CleanUp:
    rs.Close: conn.Close
    Set rs = Nothing: Set conn = Nothing
    
End Sub

Hope this helps. :)

Destined

Destined:

I tested your code on a seperate form to see if I can get the recordset to move as desired and it only moved one record then stopped:

Here is the code I used:

VB Code:

Private Sub Command1_Click()
Dim recordToDisplay As Integer
Dim msgbtn As Integer, strSelect As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim counter As Integer
 
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open "C:\My Documents\SalesTracking.mdb"
 
rst.Open "SELECT * FROM Main", con
 
recordToDisplay = recordToDisplay + 1
 
counter = 0
    While counter < recordToDisplay And Not rst.EOF
        counter = counter + 1
        rst.MoveNext
    Wend
 
If rst.EOF Then
    MsgBox "Cannot go past last record."
    recordToDisplay = recordToDisplay - 1
    GoTo CleanUp
Else
    txtCstmrFName.Text = rst.Fields("CustomerFName")
    txtCstmrLName.Text = rst.Fields("CustomerLName")
    txtSSNum.Text = rst.Fields("SS")
    txtSlsRefNot.Text = rst.RecordCount
End If
 
CleanUp:
 
rst.Close: con.Close
Set rst = Nothing
Set con = Nothing
 
End Sub
____________________________________________________
 
Private Sub Form_Activate()
Dim recordToDisplay As Integer
Dim msgbtn As Integer, strSelect As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim counter As Integer
 
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open "C:\My Documents\SalesTracking.mdb"
 
rst.Open "SELECT * FROM Main", con
 
recordToDisplay = recordToDisplay + 1
 
counter = 0
    While counter < recordToDisplay And Not rst.EOF
        counter = counter + 1
        rst.MoveFirst
    Wend
 
If rst.EOF Then
    MsgBox "Cannot go past last record."
    recordToDisplay = recordToDisplay - 1
    GoTo CleanUp
'BELOW is used to avoid the 'INVALID USE OF NULL' 
'error...because not all fields will have data in them
 
Else
    If txtCstmrFName.Text = "" Or txtCstmrFName.Text = Null Then
    'do nothing
    Else
    txtCstmrFName.Text = rst.Fields("CustomerFName")
    End If
    If txtCstmrLName.Text = "" Or txtCstmrLName.Text = Null Then
    'do nothing
    Else
        txtCstmrLName.Text = rst.Fields("CustomerLName")
        End If
    If txtSSNum.Text = "" Or txtSSNum.Text = Null Then
    'do nothing
    Else
    txtSSNum.Text = rst.Fields("SS")
    End If
    txtSlsRefNot.Text = rst.RecordCount
End If
 
 
 
CleanUp:
 
rst.Close: con.Close
Set rst = Nothing
Set con = Nothing
 
End Sub

mxnmx:

I emailed you a scaled down copy of my project, I hope you can help...

Thank you to all who are assisting me on this!!!

Uh.. you put the declaration "Dim recordToDisplay As Integer" inside the click button. It should be outside, thus it will save the location value.

By putting it inside, the variable is by default 0 (i think) and will never have a starting value other than that. Putting it outside, it will save its state, causing it to increment every time you click the button.

Hope this helps,

Destined

Ok Destined, I got the recordset to move next....so I will incorporate this into my real form...but one thing that I noticed is that within my code I have

VB Code:

txtSlsRefNot.Text = rst.RecordCount

This is simply to show the number of records in that query...but when I run the code the textbox show -1, when in reality it should show a much higher number...

Any idea why that happened?

Allright....so I made soem changs to my form with suggestions provided by Destined...and it still will not move to the next record...the counter will drop down by one but the textboxes will remain showing the 1st record data!!!

Once again here is my code:

VB Code:

Private Sub cmdNextRec_Click()
Const Formclose As Long = 3021
On Error GoTo ErrorMsg
Dim counter As Integer
Dim msgbtn As Integer, strSelect As String, msgbtn2 As Integer
Set rs = New Recordset
 
strSelect = "SELECT * FROM Main WHERE CallBackShift = '" & txtSalesShift.Text & "' AND AgentCmpltd <>'""'"
rs.Open strSelect, MyDataEnvironment.MyConnection, adOpenDynamic, adLockOptimistic
 
recordToDisplay = recordToDisplay + 1
 
If txtSaleRep.Text = "" Then
MsgBox "Please check the box to use the form before continuing!", vbCritical, "Your Name?"
chkCB.SetFocus
Exit Sub
Else
If lstAppGen.Text = "" Then
MsgBox "Please select if an application was generated!", vbCritical, "Application Generated?"
lstAppGen.SetFocus
Exit Sub
Else
counter = 0
    While counter < recordToDisplay And Not rs.EOF
        counter = counter + 1
        rs.MoveNext
    Wend
 
If rs.EOF Then
    msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
    recordToDisplay = recordToDisplay - 1
    GoTo CleanUp
Else
chkCB.Value = 0
rs.Fields("AgentCmpltd") = txtSaleRep.Text
rs.Fields("CmpltdDate") = txtDateCmpltd.Text
rs.Fields("AppGen") = lstAppGen.Text
rs.Update
txtSlsRefNot.Text = rs.RecordCount
txtSlsRefNot.Text = rs.RecordCount - 1
 
End If
End If
    
 
If msgbtn = vbOK Then
msgbtn2 = MsgBox("Would you like to quit the program now?", vbInformation + vbYesNo, "Quit?")
If msgbtn2 = vbYes Then
rs.Close
End
 
Else
'do nothing
End If
End If
End If
DisplayEnd:
    Exit Sub
 
ErrorMsg:
    If Err = Formclose Then
    frmMsgboxLstRec.Show
   End If
Resume DisplayEnd
 
CleanUp:
rs.Close
Set rs = Nothing
 
End Sub
____________________________________________________
'This subroutine seems to work fine...it is providing the 1st record
 'from the SQL query
 
Private Sub Form_Activate()
Dim msgbtn As Integer, strSelect As String
Set rs = New Recordset
 
strSelect = "SELECT * FROM Main WHERE CallBackShift = '" & txtSalesShift.Text & "' AND AgentCmpltd <>'""'"
rs.Open strSelect, MyDataEnvironment.MyConnection, adOpenDynamic, adLockOptimistic
 
 
If rs.RecordCount = 0 Then
msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
Else
    rs.MoveFirst
    txtDateRef.Text = rs.Fields("DateRef")
    txtCstmrFName.Text = rs.Fields("CustomerFName")
    txtCstmrLName.Text = rs.Fields("CustomerLName")
    txtSSNum.Text = rs.Fields("SS")
    txtHSBCAcct.Text = rs.Fields("ExsistAcct")
    If txtBrochOnly.Text = "" Or Null Then
        'do nothing
    Else
        txtBrochOnly.Text = rs.Fields("BrochOnly")
    End If
    If txtBrochAddress.Text = "" Or Null Then
        'do nothing
    Else
        txtBrochAddress.Text = rs.Fields("BrochAddress")
    End If
    txtCBPref.Text = rs.Fields("CallbackDate")
    txtBestTime.Text = rs.Fields("BestTime")
    txtCBShift.Text = rs.Fields("CallBackShift")
    txtPriPhn.Text = rs.Fields("PrimaryPhone")
    
        If txtCallBackCmmnts.Text = "" Or Null Then
            'do nothing
        Else
            txtCallBackCmmnts.Text = rs.Fields("CallbackComments")
        End If
        If txtSecPhn.Text = "" Or Null Then
            'do nothing
        Else
            txtSecPhn.Text = rs.Fields("SecPhone")
        End If
        If txtChecking.Text = "" Or Null Then
            'do nothing
        Else
            txtChecking.Text = rs.Fields("CHECKING")
        End If
        If txtSavings.Text = "" Or Null Then
            'do nothing
        Else
            txtSavings.Text = rs.Fields("SAVINGS")
        End If
        If txtCredit.Text = "" Or Null Then
            'do nothing
        Else
            txtCredit.Text = rs.Fields("CREDIT CARD")
        End If
        If txtATM.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtATM.Text = rs.Fields("ATM/MASTERMONEY")
        End If
        If txtSelect.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtSelect.Text = rs.Fields("SELECT CREDIT")
        End If
        If txtInsurance.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtInsurance.Text = rs.Fields("INSURANCE")
        End If
        If txtInvestments.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtInvestments.Text = rs.Fields("INVESTMENTS")
        End If
        If txtCD.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtCD.Text = rs.Fields("CD")
        End If
        If txtHomeEquity.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtHomeEquity.Text = rs.Fields("HOME EQUITY")
        End If
        If txtInstallment.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtInstallment.Text = rs.Fields("INSTALLMENT")
        End If
        If txtTuition.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtTuition.Text = rs.Fields("TUITION PLUS LOC")
        End If
        If txtOther.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtOther.Text = rs.Fields("OTHER")
        End If
        If txtFinalCmmnts.Text = "" Or Null Then
            'DO NOTHING
        Else
            txtFinalCmmnts.Text = rs.Fields("FinalComments")
        End If
                   
txtSlsRefNot.Text = rs.RecordCount
End If
 
If msgbtn = vbOK Then
Unload Me
 
End If
 
 
rs.Close
 
End Sub

I am sure that I am doing something wrong :confused: :confused:

I got your project, I'm sorry to say but its not at all USER FRIENDLY. So you need to pull your socks up and work on it. The interface that you have can overwhelm the user, textboxes are all over the place...:D
So take your first step on improving the interface and arrangments of all your controls.

Your code is all complicated too, no commenting, no indenting!

So what you should do is that- remove the dataenvironment and connect to your database like this:

VB Code:

'Add a reference to ADO 2.7
Dim getCnn As ADODB.Connection
Dim Rs As ADODB.Recordset
 
Set getCnn = New ADODB.Connection
getCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SalesTracking.mdb;"
getCnn.Open
 
Set Rs = New Recordset
Rs.Open Table1, getCnn, adOpenKeyset, adLockOptimistic
'Because you need to keep accessing your db all the time.
'Once you have opened the datasource whether a Table or SQL   
'query you can simply get data from it by:
 
Text1.Text=rs("Field Name")
 
'And you can write to the db like this
 
rs("Field Name")=Text1.Text
 
'And you can move on like this
 
rs.MoveNext

You need to do improve your code so we can understand it here, and make your interface better so that the users of your app appreciate your work...

:D :D

mxnmx:

I appreciate your candid response....I agree on all counts...this is by the way my first attempt at a project, and I agree that I can improve upon it greatly!!!

I knew about the ADO connection through VBA, but when I got the VB6 and saw the dataenvironment, I thought that it would save time....

But as I can see from many of you pro's in here, that sometimes the shortest line is not always the best!

So I will "pull up my socks" and hit this again, with all of your suggestions in mind!

Thank you once again...

If I run into problem with the record not moving I will let you know.

:D

Sincerely,
Salvatore

Hello mxnmx:

Below is a revised version of 2 subroutines that I redid using your suggestions....I hope it looks better!

The problem is that the recordset is till not moving to the next record...I can only guess that it has to do with the placement of the rs.MoveNext within the "cmdNextRec" click event:

VB Code:

Private Sub cmdNextRec_Click()
Const Formclose As Long = 3021
On Error GoTo ErrorMsg
    'set ADO connection
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSelect As String, msgbtn As Integer, msgbtn2 As Integer
 
Set con = New ADODB.Connection
 
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\SalesTracking.mdb"
con.Open
 
Set rs = New Recordset
'Setup query to pull only records that have not been looked at by a sales rep and
'...called back
 
strSelect = "SELECT * FROM Main WHERE CallBackShift = '" & txtSalesShift.Text & "' AND AgentCmpltd <>'""'"
rs.Open strSelect, MyDataEnvironment.MyConnection, adOpenKeyset, adLockOptimistic
 
'we need to check and make sure that the user complete's the appropriate fields
'...before continuing!
 
If txtSaleRep.Text = "" Then
    MsgBox "Please check the box to use the form before continuing!", vbCritical, "Your Name?"
    chkCB.SetFocus
    Exit Sub
Else
If lstAppGen.Text = "" Then
    MsgBox "Please select if an application was generated!", vbCritical, "Application Generated?"
    lstAppGen.SetFocus
    Exit Sub
Else
    'At the end of the recordset alert the user and close the connection
If rs.EOF Then
    msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
    GoTo CleanUp
    
    'otherwise move to the next record within the select query and update the
    '...appropriate fields
Else
    rs.MoveNext
    chkCB.Value = 0
    rs("AgentCmpltd") = txtSaleRep.Text
    rs("CmpltdDate") = txtDateCmpltd.Text
    rs("AppGen") = lstAppGen.Text
    txtSlsRefNot.Text = rs.RecordCount
    txtSlsRefNot.Text = rs.RecordCount - 1
 
End If
    End If
    
    'Ask the user if they want to quit the program since there are no more records
If msgbtn = vbOK Then
    msgbtn2 = MsgBox("Would you like to quit the program now?", vbInformation + vbYesNo, "Quit?")
If msgbtn2 = vbYes Then
    rs.Close
    End
 
Else
    'do nothing
End If
    End If
        End If
        
    'To avoid the error '3021
DisplayEnd:
    Exit Sub
 
ErrorMsg:
    If Err = Formclose Then
    frmMsgboxLstRec.Show
   End If
Resume DisplayEnd
 
    'close connections
CleanUp:
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
 
End Sub
____________________________________________________
 
 
Private Sub Form_Activate()
Dim msgbtn As Integer, strSelect As String
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
 
    'open the ADO connection to the Access database
Set con = New ADODB.Connection
 
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\SalesTracking.mdb"
con.Open
 
Set rs = New Recordset
 
    'set up the SELECT query to pull only customers who have not been called back
    
strSelect = "SELECT * FROM Main WHERE CallBackShift = '" & txtSalesShift.Text & "' AND AgentCmpltd <>'""'"
rs.Open strSelect, MyDataEnvironment.MyConnection, adOpenKeyset, adLockOptimistic
 
    'check to see if there are records in the query, if not then move to the first
    'record and fills the textboxes with the fields in the query
    
If rs.RecordCount = 0 Then
    msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
Else
    rs.MoveFirst
    txtDateRef.Text = rs("DateRef")
    txtCstmrFName.Text = rs("CustomerFName")
    txtCstmrLName.Text = rs("CustomerLName")
    txtSSNum.Text = rs("SS")
    txtHSBCAcct.Text = rs("ExsistAcct")
    
    'The following If statements are to avoid a "Invalid Null" error message
    'since these fields may or may not have data in them
    
If txtBrochOnly.Text = "" Or Null Then
    'do nothing
Else
    txtBrochOnly.Text = rs("BrochOnly")
End If
 
If txtBrochAddress.Text = "" Or Null Then
    'do nothing
Else
    txtBrochAddress.Text = rs("BrochAddress")
End If
 
    txtCBPref.Text = rs("CallbackDate")
    txtBestTime.Text = rs("BestTime")
    txtCBShift.Text = rs("CallBackShift")
    txtPriPhn.Text = rs("PrimaryPhone")
    
If txtCallBackCmmnts.Text = "" Or Null Then
     'do nothing
Else
     txtCallBackCmmnts.Text = rs("CallbackComments")
End If
 
If txtSecPhn.Text = "" Or Null Then
    'do nothing
Else
    txtSecPhn.Text = rs("SecPhone")
End If
 
If txtChecking.Text = "" Or Null Then
    'do nothing
Else
    txtChecking.Text = rs("CHECKING")
End If
 
If txtSavings.Text = "" Or Null Then
    'do nothing
Else
    txtSavings.Text = rs("SAVINGS")
End If
 
If txtCredit.Text = "" Or Null Then
    'do nothing
Else
    txtCredit.Text = rs("CREDIT CARD")
End If
 
If txtATM.Text = "" Or Null Then
    'DO NOTHING
Else
    txtATM.Text = rs("ATM/MASTERMONEY")
End If
 
If txtSelect.Text = "" Or Null Then
    'DO NOTHING
Else
    txtSelect.Text = rs("SELECT CREDIT")
End If
 
If txtInsurance.Text = "" Or Null Then
    'DO NOTHING
Else
    txtInsurance.Text = rs("INSURANCE")
End If
 
If txtInvestments.Text = "" Or Null Then
    'DO NOTHING
Else
    txtInvestments.Text = rs("INVESTMENTS")
End If
 
If txtCD.Text = "" Or Null Then
    'DO NOTHING
Else
    txtCD.Text = rs("CD")
End If
 
If txtHomeEquity.Text = "" Or Null Then
    'DO NOTHING
Else
    txtHomeEquity.Text = rs("HOME EQUITY")
End If
 
If txtInstallment.Text = "" Or Null Then
    'DO NOTHING
Else
    txtInstallment.Text = rs("INSTALLMENT")
End If
 
If txtTuition.Text = "" Or Null Then
    'DO NOTHING
Else
    txtTuition.Text = rs("TUITION PLUS LOC")
End If
 
If txtOther.Text = "" Or Null Then
    'DO NOTHING
Else
    txtOther.Text = rs("OTHER")
End If
 
If txtFinalCmmnts.Text = "" Or Null Then
    'DO NOTHING
Else
    txtFinalCmmnts.Text = rs("FinalComments")
End If
 
    'show the total number of records in the query
txtSlsRefNot.Text = rs.RecordCount
End If
 
    'when there are no more records then close the form, and close connections
 
If msgbtn = vbOK Then
    Unload Me
 
End If
 
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
 
End Sub

Hey Salvatore,

If you could, give an explanation, in english, what you'd like to happen when you click on NextRecord?

Is it something like:

When user clicks button, validate the values entered by the user. If they're valid, update the corresponding recordset values to the ones the user entered, then increment the current record and display the values stored.

Is this what you want?

I noticed in your code that you do the sql query, but only after you've gotten the recordset do you validate the inputs. I'm guessing you'd probably want to change this order.

As well, you're still not moving to the recordset that you're wanting to... verify my story and I'll give you a decent layout of what you probably want.

Destined

Assuming that my description is what you'd like to do, here's some sample code that opens an Access DB, and cycles back and forward through it, allowing for updating of the information. However, no code has been entered for Adding or Deleting an entire record.

Hope this helps.

Destined

Destined:

What I am trying to accomplish to move through a recordset and update 3 fields.

SO I have reps who will access this form and open to the first record...conduct the call back. They will need to enter their name and date and if an application was generated...so when they fill in these fields it should update the database when they go to the next record!

I tried your suggestions, but i was not successful in updating the recordset...( im sure I ma doing something wrong!)

I just need to know with the code I submittted last, what order the rs.MoveNext needs to be in, in relation to the rs.update, and how to show the fields from the next record????

I hope this clarifies things

Can someone please larify something for me????????????

I am still working on this stupid problem...I went back to my form and returned the textboxes to bound controls using a Dataenvironment...when I used the rs.MoveNext everything worked great....the only problem with that is I can not query using specific criteria!!!

When I used an ADO connection I was unable to get the same results..

VB Code:

Private Sub Form_Activate ()
Dim msgbtn As Integer, strSelect As String
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
 
    'open the ADO connection to the Access database
Set con = New ADODB.Connection
 
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\SalesTracking.mdb"
con.Open
 
Set rs = New Recordset
 
    'set up the SELECT query to pull only customers who have not been called back
    
strSelect = "SELECT * FROM Main WHERE CallBackShift = '" & txtSalesShift.Text & "' AND AgentCmpltd <>'""'"
rs.Open strSelect, con, adOpenKeyset, adLockOptimistic
 
    'check to see if there are records in the query, if not then move to the first
    'record and fills the textboxes with the fields in the query
    
If rs.RecordCount = 0 Then
    msgbtn = MsgBox("There are currently no Call Back records!", vbInformation, "No Records")
Else
    rs.MoveFirst
    txtDateRef.Text = rs("DateRef")
    txtCstmrFName.Text = rs("CustomerFName")
    txtCstmrLName.Text = rs("CustomerLName")
    txtSSNum.Text = rs("SS")
    txtHSBCAcct.Text = rs("ExsistAcct")
    
    'The following If statements are to avoid a "Invalid Null" error message
    'since these fields may or may not have data in them
    
If txtBrochOnly.Text = "" Or Null Then
    'do nothing
Else
    txtBrochOnly.Text = rs("BrochOnly")
End If
 
If txtBrochAddress.Text = "" Or Null Then
    'do nothing
Else
    txtBrochAddress.Text = rs("BrochAddress")
End If
 
    txtCBPref.Text = rs("CallbackDate")
    txtBestTime.Text = rs("BestTime")
    txtCBShift.Text = rs("CallBackShift")
    txtPriPhn.Text = rs("PrimaryPhone")
    
If txtCallBackCmmnts.Text = "" Or Null Then
     'do nothing
Else
     txtCallBackCmmnts.Text = rs("CallbackComments")
End If
 
If txtSecPhn.Text = "" Or Null Then
    'do nothing
Else
    txtSecPhn.Text = rs("SecPhone")
End If
 
If txtChecking.Text = "" Or Null Then
    'do nothing
Else
    txtChecking.Text = rs("CHECKING")
End If
 
If txtSavings.Text = "" Or Null Then
    'do nothing
Else
    txtSavings.Text = rs("SAVINGS")
End If
 
If txtCredit.Text = "" Or Null Then
    'do nothing
Else
    txtCredit.Text = rs("CREDIT CARD")
End If
 
If txtATM.Text = "" Or Null Then
    'DO NOTHING
Else
    txtATM.Text = rs("ATM/MASTERMONEY")
End If
 
If txtSelect.Text = "" Or Null Then
    'DO NOTHING
Else
    txtSelect.Text = rs("SELECT CREDIT")
End If
 
If txtInsurance.Text = "" Or Null Then
    'DO NOTHING
Else
    txtInsurance.Text = rs("INSURANCE")
End If
 
If txtInvestments.Text = "" Or Null Then
    'DO NOTHING
Else
    txtInvestments.Text = rs("INVESTMENTS")
End If
 
If txtCD.Text = "" Or Null Then
    'DO NOTHING
Else
    txtCD.Text = rs("CD")
End If
 
If txtHomeEquity.Text = "" Or Null Then
    'DO NOTHING
Else
    txtHomeEquity.Text = rs("HOME EQUITY")
End If
 
If txtInstallment.Text = "" Or Null Then
    'DO NOTHING
Else
    txtInstallment.Text = rs("INSTALLMENT")
End If
 
If txtTuition.Text = "" Or Null Then
    'DO NOTHING
Else
    txtTuition.Text = rs("TUITION PLUS LOC")
End If
 
If txtOther.Text = "" Or Null Then
    'DO NOTHING
Else
    txtOther.Text = rs("OTHER")
End If
 
If txtFinalCmmnts.Text = "" Or Null Then
    'DO NOTHING
Else
    txtFinalCmmnts.Text = rs("FinalComments")
End If
 
    'show the total number of records in the query
txtSlsRefNot.Text = rs.RecordCount
End If
 
    'when there are no more records then close the form, and close connections
 
If msgbtn = vbOK Then
    Unload Me
 
End If
 
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
 
End Sub

This code fills the fields correctly...but it is when the user enters data into a field to change the underlying database table and then move to the next record, is where I am pulling my hair!!!:mad:

Logic tells me that when I want to move to a new record I have to populate the fields with data from the next record, while also updating the last one...

Can someone please help me to understand what I am doing wrong????

Hey Salvatore,

I was wondering if you've had time to look at my code? I'm pretty sure it does exactly what you want to do, although it's done on a different database.

If you haven't had a look at it, I suggest you do as it might help you with your problem.

If not, email me your code. destined_soul@_________ where you fill the ___ with hotmail.com.

Destined

Destined:

I got an opportunity to work on this form using your suggestions....and I ran into a familiar problem with the rs.MoveFirst function:

VB Code:

Private Sub Form_Load()
 
    Dim sqlStr As String
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open App.Path & "\SalesTracking.mdb"
    conn.BeginTrans
    
    sqlStr = "SELECT * FROM Main WHERE CallBackShift = '" & txtSalesShift.Text & "' AND AgentCmpltd <>'""'"
    
    ' If I recall correctly, only adOpenStatic or adOpenKeyset actually
    ' fills rs.recordCount with a value other than -1
    
    rs.Open sqlStr, conn, adOpenStatic, adLockOptimistic
    rs.MoveFirst
    
    If rs.EOF Then
        MsgBox "No records found, exiting program"
        Unload Me
        End
    End If
    
    rs.MoveFirst
    
    FillInformation
    AdjustEnabledButtons
    
    currIndex = 1
    modificationsApplied = False
    
End Sub

I am getting run-time error3021 - Either EOF or BOF is True, or the current record has been deleted; it requires a current record!

I can not seem to get past this...there are records in the database???

I think I know why you're getting that strange error, and it's quite logical. I've been playing with your code for a bit, and the whole time, nothing happened... until now. I looked over the query, and what it turns out is that the fields are (somehow) non-null, but that they're empty strings!!!!

Thus "WHERE field IS Null" will fail.

I'm not sure of the "proper" syntax, but here's what I found to use after a little bit:

"WHERE (AgentCmpltd IS Null OR Ltrim(Rtrim(AgentCmpltd)) = '')"

Note that the LTrim(....) = '' uses two single quotes ', not a double " quote.

Destined

Destined:

It is still unsuccessful!!! When I first run the form, it appears to show the accurate first record in the table, but when I attempt to change the data in 3 of the fields and then move to the next record, NOTHING happens!

I know that I am still learning, but I am curious, within the code of the cmdNextRec:

VB Code:

If Not UpdateDBInformation Then Exit Sub
 
rs.MoveNext
currIndex = currIndex +1
 
FillInformation
AdjustEnableButtons

But how does the rs.MoveNext function know to use the appropriate recordset???

Because the query is opened within the form load...and the cmdNextRec (from what I can see) is only referring to the variable rs which is declared as a Recordset....could this be why it is not moving?

The other thing is that when I add a name to the Sales Rep field while in run mode, and then Quit, it is not updating the underlying table....:confused:

I will be working on this problem all day today, so if there is anything that comes to mind, please do not hesitate to let me know

THANK YOU!

Hey Salvatore,

I'm just getting ready for work, but here's some stuff to think about.

Quote:

---

But how does the rs.MoveNext function know to use the appropriate recordset???

---

Ah. This is probably the biggest issue you have to deal with. Although your code has the ability to work, you need to ask yourself what the user should do. Does the user HAVE TO fill out the current record being displayed before moving to the next record? For the moment, your code forces you to have to finish dealing with that one record.

Although I am not the designer of the program, I'd suggest looking into maybe having MoveNext/Prev just cycle through the database without applying any changes to the recordset. To be able to 'save' changes to a record, I'd probably make a 'Save Changes' button that checks the fields and then updates the recordset if everything's ok. If you did this, you would also want to ask if you'd requery the database once a save was made.

I say this because once I got the program working (ish) on my end, it always wanted to update the fields even if I didn't change anything. Another idea may be to just have it message ("Some fields are invalid, would you like to not save them and continue?", vbYesNo... if yes then just movenext, else highlight error.) Again, these are just some thoughts. You probably have an idea of what it should do, I'd hope. ;) hehee...

Quote:

---

Because the query is opened within the form load...and the cmdNextRec (from what I can see) is only referring to the variable rs which is declared as a Recordset....could this be why it is not moving?

---

No. It is not moving because UpdateDBInformation is return false. In the code, you have

VB Code:

Private Function UpdateDBInformation() As Boolean
    If rs.EditMode = adEditInProgress Then
        ...
    End If
End Function

This is a problem, since the only place in your code where you edit the recordset (ie: rs!Name = "Bob") within the if statement!! By default, a boolean function will return false. Thus, in your cmdMoveNext, you will always exit the sub before being able to call rs.MoveNext. Solution: you don't need the if rs.EditMode = ..., just cut that if statement out from around the inner code (and it's "end if") and you should see it move...

Quote:

---

The other thing is that when I add a name to the Sales Rep field while in run mode, and then Quit, it is not updating the underlying table....

---

Ah, yes. Well, since you will never get into updating the fields in the recordset, you will never set your flag "modificationsApplied" to True. Thus, every time the form unloads, you will rollback the transaction.

Hope some of this helps. I'll check with you throughout the day when I can. :)

Oh, one more thing. You should try indenting a little more so I don't have to when I get the program. :P Just makes it a lot easier to read the logic of the program. I'm not sure where some good pointers are, though.

Oh well, off to work. (Summer student, temp, doing a vb interface for a ms access db. :))

Destined