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?
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!!
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....
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
Last edited by Destined Soul; Jun 19th, 2003 at 11:37 AM.
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.
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...
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 <>'""'"
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...
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:
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.
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
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.
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.
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????
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..
'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!!!
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????
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.
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.
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....
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
I'm just getting ready for work, but here's some stuff to think about.
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...
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...
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. )
Reply With Quote
hehee...