[RESOLVED] Code works on buttonclick, but not automatically

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

VB Code:

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

Quote:

---

Originally Posted by Runtime Error '2115'

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

VB Code:

txtCourse.Text = ""

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

VB 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 :)

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..

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

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..

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

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

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

VB Code:

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

VB Code:

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.

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??

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 :/

VB Code:

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.**

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

E.G.

VB Code:

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

can just be

VB Code:

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

And it will still work..

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

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

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

VB Code:

'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..

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

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 :/

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..

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?

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

im using VBA (Access)

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

And also,

VB Code:

lngVal = CLng(ID.Column(1))

Wouldnt work, so I changed it to

VB Code:

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

Can you foresee any problems with me doing this?

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

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

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

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

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..

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

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