Access 2007 ADO the value you entered isn't valid for this field

[jbakerstull]

I'm trying to create cmd button to move to the next record using code below. When I test the code I receive a run time error stating "The value you entered isn't valid for this field. Error occurs at Me.cbostrProviderName = rst!strProviderName

strproviderName is a string value.

Private Sub Command33_Click()
Set conn = CurrentProject.AccessConnection
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM tblEligibilityCharacteristics Where [Unique] = " & Chr(34) & Me.txtUnique & Chr(34), _
conn, adOpenForwardOnly, adLockReadOnly
Do While Not rst.EOF
rst.MoveNext
Loop

counter = counter + 1
Me.cmdFirst.Enabled = True

Me.cbostrProviderName = rst!strProviderName


Me.cbostrSubsidy = rst!strSubsidy
Me.txtintNoUnits = rst!intNoUnits
Me.txtUnique = rst!Unique

Me.cmdPrevious.Enabled = True
Me.lbRecordNo.Caption = counter
Me.cbostrProviderName.SetFocus
If counter = rst.RecordCount Then
Me.cmdNext.Enabled = False
Me.Command33.Enabled = False
Me.cmdLast.Enabled = False
End If



Set rst = Nothing
Set conn = Nothing

End Sub

[ajetrumpet]

I'm trying to create cmd button to move to the next record using code below.

moving to the next record in your FORM??

[jbakerstull]

yes,, on the onload, the recordset goes to the first record. Couldn't a command button using movenext go to the next record??

Set conn = CurrentProject.AccessConnection
Set rst = New ADODB.Recordset ' <--instantiate the recordset before opening
With rst
.Open "SELECT * FROM tblEligibilityCharacteristics", conn, adOpenKeyset, _
adLockOptimistic, adCmdText
.MoveFirst
End With


counter = 1
Call FillTxtBoxes1(rst, Me)


Me.cbostrProviderName = rst!strProviderName
Me.cbostrSubsidy = rst!strSubsidy
Me.txtintNoUnits = rst!intNoUnits
Me.txtUnique = rst!Unique


Me.txtUnique.SetFocus
Me.cmdFirst.Enabled = False
Me.cmdPrevious.Enabled = False
Me.lbRecordNo.Caption = counter

Set rst = Nothing
Set conn = Nothing

[ajetrumpet]

Couldn't a command button using movenext go to the next record??

YES!! that's exactly why I asked what I did. that's one line of code. first question on my mind was why you weren't using that simple method, but instead writing all of this irrelevant code.

I would use MOVENEXT. good thought sir.

[khalid]

Hi jbakerstull,

.Open "SELECT * FROM tblEligibilityCharacteristics", conn, adOpenKeyset, _
adLockOptimistic, adCmdText
.MoveFirst
End With

Using just SELECT * FROM tblEligibilityCharacteristics would not work, if you are calling the rst!strProviderName.
instead you have to tell in your SELECT Statement the column names i.e SELECT tblEligibilityCharacteristics.strProviderName for all your fields you want to give the values to your controls on form.

Hope this make sense.

[ajetrumpet]

khalid,

are you saying the "*" doesn't work in ADO?? not that I'm surprised, but I've never tried it...

[khalid]

Adam its work in both ADO and DAO
but the problem is here the OP wants:

Me.cbostrProviderName = rst!strProviderName
Me.cbostrSubsidy = rst!strSubsidy
Me.txtintNoUnits = rst!intNoUnits
Me.txtUnique = rst!Unique

Which produce Error where there is no such column names called in the SELECT statement.
Yeah this happens

[nicknameoscar]

'SQL to find the entry/division combo that is passed into this function
strSQL = "SELECT * FROM tblDivisionDetails " & _
"WHERE (((tblDivisionDetails.DivisionNo)=" & slngDivisionNo & ") AND " & _
"((tblDivisionDetails.ShowDetailsID)=" & slngShowDetailsID & "));"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset 'Allow forward and backward movement in recordset
.LockType = adLockOptimistic 'Lock record after Update method is issued
.Open strSQL
 
If .BOF And .EOF Then
 
.Close
Set rst = Nothing
Exit Sub
 
End If

The above works fine for me. Are you perhaps referring to using "*" in a WHERE clause used by ADO. I had problems with that until I learned that I had to use "%" for ADO.

Sorry, I was writing the above when khalid posted his response.

[khalid]

The above works fine for me. Are you perhaps referring to using "*" in a WHERE clause used by ADO. I had problems with that until I learned that I had to use "%" for ADO.

This is fine for just moving through records, but if you use SELECT * and then use the somecontrolOnForm = rs!Fieldname
this will produce an Error.

[nicknameoscar]

So I can make logic decisions on fields (see red text below):

'SQL to find the entry/division combo that is passed into this function
strSQL = "SELECT * FROM tblDivisionDetails " & _
"WHERE (((tblDivisionDetails.DivisionNo)=" & slngDivisionNo & ") AND " & _
"((tblDivisionDetails.ShowDetailsID)=" & slngShowDetailsID & "));"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset 'Allow forward and backward movement in recordset
.LockType = adLockOptimistic 'Lock record after Update method is issued
.Open strSQL
 
If .BOF And .EOF Then
 
.Close
Set rst = Nothing
Exit Sub
 
End If
 
'the only time all 3 of the below variables would be true is if the user wants to delete the entire
'division from tblDivisionDetails
If sblnA And sblnB And sblnC Then
 
'if the entry is marked as having exhibited in any class in this division then provide the
' user with a strong warning and give them the option of selecting Yes - delete record or
' No - do not delete record
If !ExhibitedA Or !ExhibitedB Or !ExhibitedC Then

But if I tried:

me.checkboxOnSomeForm = !ExhibitedA

(ExhibitedA is a Yes/No field in that table.) it would fail? Interesting!

Red text did not show but tags are there.

[jbakerstull]

If I'm unable to use "*" should the statment be written like:

Set conn = CurrentProject.AccessConnection
Set rst = New ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "tblEligibilityCharacteristics", conn, adOpenKeyset, adLockOptimistic
rst.MoveNext
rst.Find "'[Unique] = '" & txtUnique.Value
If rst.EOF Then
MsgBox "Client Record Not Found"
Else
Me.cbostrProviderName = rst!strProviderName
Me.cbostrSubsidy = rst!strSubsidy
Me.txtintNoUnits = rst!intNoUnits
Me.txtUnique = rst!Unique

counter = counter + 1
Me.cmdFirst.Enabled = True

Me.cbostrProviderName = Null
Me.cbostrSubsidy = Null
Me.txtintNoUnits = Null
Me.txtUnique = Null


Me.cmdPrevious.Enabled = True
Me.lbRecordNo.Caption = counter
Me.cbostrProviderName.SetFocus
If counter = rst.RecordCount Then
Me.cmdNext.Enabled = False
Me.Command33.Enabled = False
Me.cmdLast.Enabled = False
End If



Set rst = Nothing
Set conn = Nothing
End If
End Sub


I tried replacing the "*" with specify fields names but I still received the same error message.

[khalid]

I am sorry, but this is working fine:

me.checkboxOnSomeForm = !ExhibitedA

if you use SELECT * in the recordset will get the column name, but its happened somewhere sometime with just Selecting only * in the select statement I remember.... so I only call those columns in select statement which I need.
sorry again if i miss guided:

[khalid]

If I'm unable to use "*" should the statment be written like:

Set conn = CurrentProject.AccessConnection
Set rst = New ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblEligibilityCharacteristics", conn, adOpenKeyset, adLockOptimistic
rst.MoveNext
rst.Find "'[Unique] = '" & txtUnique.Value &''"
If rst.EOF Then
MsgBox "Client Record Not Found"
Else
Me.cbostrProviderName = rst!strProviderName
Me.cbostrSubsidy = rst!strSubsidy
Me.txtintNoUnits = rst!intNoUnits
Me.txtUnique = rst!Unique

counter = counter + 1
Me.cmdFirst.Enabled = True

Me.cbostrProviderName = Null
Me.cbostrSubsidy = Null
Me.txtintNoUnits = Null
Me.txtUnique = Null


Me.cmdPrevious.Enabled = True
Me.lbRecordNo.Caption = counter
Me.cbostrProviderName.SetFocus
If counter = rst.RecordCount Then
Me.cmdNext.Enabled = False
Me.Command33.Enabled = False
Me.cmdLast.Enabled = False
End If



Set rst = Nothing
Set conn = Nothing
End If
End Sub


I tried replacing the "*" with specify fields names but I still received the same error message.

please try as highlighted in the Red.

[jbakerstull]

Khalid,,

thank You.. I had to take a small victory lap around the building.. ok.. I small walk to water cooler with a shitty grin on my face.. but thanks again.

final code
Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Dim counter As Integer
Set conn = CurrentProject.AccessConnection
Set rst = New ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblEligibilityCharacteristics", conn, adOpenKeyset, adLockOptimistic
rst.Find "[Unique] = '" & txtUnique & "'"
rst.MoveNext
If rst.EOF Then
MsgBox "Error"
Else
Me.cbostrProviderName = rst!strProviderName
Me.cbostrSubsidy = rst!strSubsidy
Me.txtintNoUnits = rst!intNoUnits
Me.txtUnique = rst!Unique

counter = counter + 1
Me.cmdFirst.Enabled = True

Me.cmdPrevious.Enabled = True
Me.lbRecordNo.Caption = counter
Me.cbostrProviderName.SetFocus
If counter = rst.RecordCount Then
Me.cmdNext.Enabled = False
Me.Command33.Enabled = False
Me.cmdLast.Enabled = False
End If

Set rst = Nothing
Set conn = Nothing
End If
End Sub

[khalid]

no problem dude!
please also check for ADO references in VBA while you are using ADODB.Recordset.

In VBA mode >> Tools>>Reference >>> Microsoft ActiveX Data Objects Library xxxx