By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

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

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdbtnQuery_Click()
  2. me.subformSupplyRqmtsUpdate.Requery
Edit Button Code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdbtnEdit_Click()
  2. If Not (Me.subformSupplyRqmtsUpdate.Form.Recordset.EOF and Me.subformSupplyRqmtsUpdate.Form.Recordset.BOF) Then
  3. With Me.txtSupplyRqmtsID = .Fields("SupplyRqmtsID")
  4. Me.cboUsingLoc = .Fields("UsingLoc")
  5. Me.txtFicalYear = .Fields("FiscalYear")
  6. Me.txtState = .Fields("State")
  7. 'store id of Supply Rqmts in Tag of txtSupplyRqmtsId in case ID is modified
  8. Me.txtSupplyRqmtsID.Tag = .Fields("SupplyRqmtsID")
Update Button Code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdbtnEdit_Click()
  2. CurrentDb.Execute "UPDATE tblSupplyItemRequirement " & _
  3. " SET FiscalYear=" & Me.txtFiscalYear & _
  4. ", UsingLoc='" & Me.cboUsingLoc & "'" & _
  5. ", State='" & me.txtState & "'" & _
  6. "WHERE SupplyRqmtsID=" & Me.txtWrmRqmtsID.Tag
Clear Button Code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdbtnEdit_Click()
  2. When Button is clicked clears all the fields on the main form to Reset the form to query or Add
  3. Me.txtWrmRqmtsID.Tag = Null
  4. Me.txtFiscalYear = Null
  5. Me.cboUsingLoc = Null
  6. 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
Dec 19 '16 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
Hi

A Field returned by the form query that does not have a bound control cannot be referenced like this
Expand|Select|Wrap|Line Numbers
  1.  .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 :-

Expand|Select|Wrap|Line Numbers
  1. MsgBox Me.Recordset("SupplyRqmtsID")
  2. MsgBox Me.Recordset.Fields("SupplyRqmtsID")
or even
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 20 '16 #2

P: 1
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
Apr 26 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.