[RESOLVED] Code works on buttonclick, but not automatically

[kfcSmitty]

Right now on my program I have it so when the user clicks on a certain item in the combobox, it moves to that item. All I am using is


Do Until ID.text = newValue
If newValue > oldValue then
Me.RecordSet.MoveNext
Else
Me.RecordSet.MovePrevious
End If
Loop


Now this works fine...It moves the bound items to the correct record, however, if I allow the form_current event to run directly after this, I receive the error


The macro or function set to the BeforeUpdate or ValidationRule for this field is preventing [Beta] from saving data in the field.


This happens on line


txtCourse.Text = ""


However, if I run the above loop, and stop form_current from running. Then run a commandbutton with this code


Thinger = False
Form_Current


Where thinger is the variable I used to stop form_current from running.

The program runs fine?? :confused:


This has been stumping me for a good number of hours already, and any help would be appreciated :)

[dannymking]

Got quite a simple answer for you..

Use a completely unbound form rather than trying to bind to the underlying table and hold unbound controls as well..

You can still replicate it as tho it was actually bound by using recordset..

[kfcSmitty]

Yeah I had that before...But binding some of it was a lot less code

*sigh* okay, guess I gotta get to work

Thanks for the reply

[dannymking]

To be honest I started out with unbound forms, which makes it easier for me as the majority of the code is second knowledge now..

[kfcSmitty]

Okay, I switched everything to unbound. I am using ADO/SQL to populate everything, but I am still gettign the same error

[dannymking]

It it's not too much code could you post the code behind the form...

It could also be a table restriction that's causing this

[kfcSmitty]

Can't post the whole form...But here is what I do moving from record to record (works fine)


Private Sub cmdNext_Click()
rsWorkerForm.MoveNext
Form_Current
End Sub


And here is what I am doing when I click the combobox and attempt to move over a record


Private Sub ID_Click()
newValue = ID.Text
ID.Value = oldValue
Do Until rsWorkerForm.Fields("ID") = newValue
If newValue > oldValue Then
Dunno = True
rsWorkerForm.MoveNext
ElseIf newValue < oldValue Then
Dunno = True
rsWorkerForm.MovePrevious
End If
Loop
Dunno = False
Form_Current
End Sub


The snippet directly above errors.

[dannymking]

Let me shot together an example Access db to determine the correct method..

You could also post the Form_Current code as it might be something in there..

Are you writing the data back to the table between recordset move??

[kfcSmitty]

No, I'm not writing back to the table at all. And sure, here is the code for the Form_Current, although I dont see why moving over 1 record wouldnt error, but moving over 2+ would :/


Private Sub Form_Current()
If Not RsLoad Then
RsLoad = True
Form_Load
End If
Dim strQuery As String
'strQuery = "SELECT * FROM WorkerInformation WHERE ID ='" & ID.Value & "'"
'Dim rsQuery As ADODB.Recordset
'Set rsQuery = New ADODB.Recordset
'rsQuery.Open strQuery, cn
ID.SetFocus
ID.Value = rsWorkerForm.Fields("ID")
txtName.SetFocus
txtName.Text = rsWorkerForm.Fields("Name")
Account.SetFocus
Account.Text = rsWorkerForm.Fields("Account")
Anniversary.SetFocus
Anniversary.Text = rsWorkerForm.Fields("Anniversary")
cboClassifications.Value = rsWorkerForm.Fields("Classification")
cboClassifications_Change
If cboClassifications.Value = "Support" Then
Level.SetFocus
Level.Text = rsWorkerForm.Fields("Level")
Payband.SetFocus
Payband.Text = rsWorkerForm.Fields("Payband")
Hours.SetFocus
Hours.Text = rsWorkerForm.Fields("Hours")
ElseIf cboClassifications.Value = "Academic" Then
Payband.SetFocus
Payband.Text = rsWorkerForm.Fields("Payband")
MaxPayband.SetFocus
MaxPayband.Text = rsWorkerForm.Fields("MaxPayband")
Else
txtSalary.SetFocus
txtSalary.Text = rsWorkerForm.Fields("FullSalary")
End If

Saving = True
Campustotal = 0
AnniversaryHappened = False
anniversaryCurrent = False
Call cboClassifications_Change
Account.SetFocus
Select Case Account.Text
Case "42000", "42500", "41200", "40404", "41201"
Level.Visible = True
Level_Label.Visible = True
Case Else
Level.Visible = False
Level_Label.Visible = False
End Select
Call SetVars
ID.SetFocus
ChangeMade = False
txtCourse.SetFocus
txtCourse.Text = ""
txtSplit.SetFocus
txtSplit.Text = ""
txtStartDate.SetFocus
txtStartDate.Text = ""
txtEndDate.SetFocus
txtEndDate.Text = ""
test = 1
Dim rsNumber As ADODB.Recordset
Set rsNumber = New ADODB.Recordset
ID.SetFocus
strThing = "SELECT SplitsNumber FROM Splits WHERE ID = " & "'" & ID.Text & "'"
rsNumber.Open strThing, cn, adOpenKeyset, adLockPessimistic, adCmdText
NumoSplits = rsNumber.Fields("SplitsNumber")
rsNumber.Close
testing
ID.SetFocus
oldValue = ID.Text
End Sub


**Note, there is some redundant code in there, from other attempts.**

[dannymking]

First thing.. lose all the setfocus calls as they are not needed.. reference the default Value on the controls instead

E.G.


ID.SetFocus
ID.Value = rsWorkerForm.Fields("ID")


can just be


ID.Value = rsWorkerForm.Fields("ID")


And it will still work..

Still looking... looking at adding a running count on the query of the form...

[kfcSmitty]

cool, didn't know you could do that. I always referenced the .text property

[dannymking]

Right the sequential count on the records seems to allow the Recordset.Move to work correctly...

What you need to do is take the combobox's field and perform a sequence count on this... so change the form's SQL to include this.. Presuming ID is the combo

SeqNr: Dcount("ID","WorkerInformation","ID<" ID)+1
If text based then
SeqNr: Dcount("ID","WorkerInformation","ID<'" ID & "'")+1

Now the killer part...

Remove all the code from the Form_Current event..
Create a Private Long Variable say lngCurrRec
Change the ID combobox to have the following RowSource, needs two columns with the second one hidden..

SELECT ID, DCount("ID","WorkerInformation","ID<'" & [ID] & "'")+1 AS SeqNr FROM WorkerInformation

On The Form Load set the lngCurrRec = 1
On The Form Activate Set the combobox to return the first instance of ID

On The ComboBox Change do the following


'populate the controls on the form with the seqnr set
Dim lngVal As Long
lngVal = CLng(IDCombo.Column(1))
If lngVal < CurrentNr Then
Recordset.Move -(CurrentNr - lngVal)
Else
Recordset.Move (lngVal - CurrentNr)
End If
CurrentNr = lngVal


And hopefully that should be Job done..

[dannymking]

Forgot to mention the form will need to be based on an SQL to allow the recordset.move event to work correctly..

[kfcSmitty]

Okay, I am assuming lngCurrentRec and CurrentNr are the same? Otherwise I don't see a use for lgnCurrentRec..


Also, why must I remove everything from form_current?


Ill try working on this right now :/

[dannymking]

Your assumption is correct.. got lazy and just copied the information from what I'd built..

Your form_current is trying to write the data from the recordset back into the form.. which could be causing the error.. if the form is based on an SQL statement then the Recordset.Move will handle that for you..

[kfcSmitty]

I originally used SQL to connect to the DB and that didn't work as well... Note the commented code in my form_current.

Or is there another way to connect using only SQL and not using ADO at all?

[dannymking]

You running this from VB6 or Access.. Here is a small example of what I mean..

[kfcSmitty]

im using VBA (Access)

If that is a vb6 program I cant run it for the record.

And also,


lngVal = CLng(ID.Column(1))


Wouldnt work, so I changed it to


lngVal = CLng(ID.Column(1,CurrentNr))


Can you foresee any problems with me doing this?

[dannymking]

Not it's purely Access.. only got Office and .Net at work..

[kfcSmitty]

Well, thank ya kindly. This works pretty good, although it only moves back 1 record if lngValue < CurrentNr..But I'm sure I can fix that :)

Thanks for all your help

[dannymking]

I got it working correctly between 23 records moving to number 23 and then back to 1..

[kfcSmitty]

thats weird..Because using the code you posted above..Mine only moves 1 record in each direction....

[dannymking]

Check your SeqNr in the RowSource of the Combo... sounds like it ain't correct.. it should return a count of the records irrespective of autonumber.. in my example Db I removed autonumber 5 to display this..

[dannymking]

You actually don't need the SeqNr in the SQL Query just in the ComboBox source..

Any changes made to the entries in the form will be reflected back to the table by the way.. so you might want to lock them if not allowing edits

[kfcSmitty]

Thanks again..after referencing your project I got it working perfectly