VBA Error "The Value you entered isn't valid for this Field"

[Barne6ae]

Build an input Form that a user can use to query info Edit and or Update data.

The Form has unbound txt and cbo fields that is linked to a subform. The form also has a Query, Edit, Update, Clear Button

Query Button Code

Private Sub cmdbtnQuery_Click()
me.subformSupplyRqmtsUpdate.Requery

Edit Button Code

Private Sub cmdbtnEdit_Click()
If Not (Me.subformSupplyRqmtsUpdate.Form.Recordset.EOF and Me.subformSupplyRqmtsUpdate.Form.Recordset.BOF) Then
With Me.txtSupplyRqmtsID = .Fields("SupplyRqmtsID")
Me.cboUsingLoc = .Fields("UsingLoc")
Me.txtFicalYear = .Fields("FiscalYear")
Me.txtState = .Fields("State")
'store id of Supply Rqmts in Tag of txtSupplyRqmtsId in case ID is modified
Me.txtSupplyRqmtsID.Tag = .Fields("SupplyRqmtsID")

Update Button Code

Private Sub cmdbtnEdit_Click()
CurrentDb.Execute "UPDATE tblSupplyItemRequirement " & _
" SET FiscalYear=" & Me.txtFiscalYear & _
", UsingLoc='" & Me.cboUsingLoc & "'" & _
", State='" & me.txtState & "'" & _
"WHERE SupplyRqmtsID=" & Me.txtWrmRqmtsID.Tag

Clear Button Code

Private Sub cmdbtnEdit_Click()
When Button is clicked clears all the fields on the main form to Reset the form to query or Add
Me.txtWrmRqmtsID.Tag = Null
Me.txtFiscalYear = Null
Me.cboUsingLoc = Null
Me.txtState = Null

Everything works when you first open the form, however; when you query a record and edit/update the record and then you hit the clear button to reset the form to query another record. When hit the edit button on the new info that was queried I get the following error

"The Value you entered isn't valid for this field"

when I hit debug it highlights

With Me.txtSupplyRqmtsID = .Fields("SupplyRqmtsID")

From the Edit Command VBA code

What do I need to do to fix this issue so I do not have to close out the form to query and edit another record.

Thanks

[MikeTheBike]

Hi

A Field returned by the form query that does not have a bound control cannot be referenced like this

 .Fields("SupplyRqmtsID")

Fields is not a valid property or method of a form. It is a property of a recordset.

There are a number of ways that such a field can be referenced :-

MsgBox Me.Recordset("SupplyRqmtsID")
MsgBox Me.Recordset.Fields("SupplyRqmtsID")

or even

MsgBox [SupplyRqmtsID]

All should give the same result (provided it is not null, which will result in an error message!).

You can also used the forms RecordsetClone object in place of the Recordset object.

This, obviously, will apply to all the other such references in this Sub

I also think the With without an End With may cause an issue!?

Having said all that the error message I get is
'Invalid or unqualified reference' (A2007)

So this may be a different issue??


HTH


MTB

[galaxyuniverse]

I am a Database developer from Darae suf ,Afghanistan.

well this error is happening that a form field cannot be referenced to a textbox like you did. you can do it as below

yours one :With Me.txtSupplyRqmtsID = .Fields("SupplyRqmtsID"

change it like this :

With Me.txtSupplyRqmtsID.form.recordset

Me.Schooltxt = Forms![<<if you have main form >>]![txtSupplyRqmtsID].form![SupplyRqmtsID]

i have already tested this
Good luck

Cennamanesh