the value you entered is not valid for the field (1 Viewer)

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
hi i have this problem since i created combo box with three values. Although when i select one value and press save button it saves in the table but if i want to update that specific field from the combo box on change event it throws this error while it also updates after i close this dialog box. i don't know what i am doing wrong
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
42,970
You'll have to show us your code.

I'm pretty sure you are using the Change event improperly. The Change event runs onece for each character typed. That is unlikely to be what you want. The BeforeUpdate event runs once when focus leaves the control. If you find an error, you can cancel the update and make the user try again.
 

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
hey actually, i have stated the problem below and exactly what im trying to get from it.

ID Compound structure Ap Bp vendor

As you can see the above one has table with 25 fields in it but I could give you the first 5 fields I have a form where the data will be submitted into the database. Now vendor field has three values to select from Perry ,yaws,not applicable.

if i press the save button it saves to the database. but when i go to different records by using next button user should be able to update the vendor field by the combo box selecting value and it automatically should update it into databse
i have written an update statement in the on change event but it say the followinfg error.

Ps. while creating new records on change should not update but while viewing the records onchange should be updated with the selected value in the combo box.
 

Attachments

  • t1.JPG
    t1.JPG
    34.1 KB · Views: 430
  • t2.JPG
    t2.JPG
    11.9 KB · Views: 308
Last edited:

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
Master table bound to master form
i created a combo box and changed the control source to vendor( which is field name in the master table)
so i have new record button Macro which takes all the data from the combo box also and when pressed save button it saves into master table.
but when i open the form it shows all records one by one and when pressed next button it shows second record. typically its a huge form.( this is in view stage)
Task i want to update the value selected in the combo box into the table but it throws me this error that the value you entered is not valid for the Field 'ID'
but i tried my best it is pointing the ID of the vendor table.
Also code you asked looks for onchange combox is
Dim vendor As Variant
Dim strRQL As String
Dim str2 As Integer
Dim num As Integer
num = DCount("*", "Master")
If (Form_Master.ID <= num) Then
vendor = DLookup("vendor_name", "vendortable", "ID=" & Combo683.Value & "")
Set dbs = CurrentDb()
str2 = Form_Master.ID
strRQL = "Update Master set vendor="
strRQL = strRQL & " '" & vendor & "' WHERE ID=" & str2 & ";"

CurrentDb.Execute strRQL
dbs.Close

That error is poped when the the lines of code is executed but as soon as when i press the next button its shows that error. but my update was made into the table still shows the error i wonder why
Also if i click the refresh all button which is on top then the error doesn't pop weird
 

Solo712

Registered User.
Local time
Today, 11:48
Joined
Oct 19, 2012
Messages
828
Master table bound to master form
i created a combo box and changed the control source to vendor( which is field name in the master table)
so i have new record button Macro which takes all the data from the combo box also and when pressed save button it saves into master table.
but when i open the form it shows all records one by one and when pressed next button it shows second record. typically its a huge form.( this is in view stage)
Task i want to update the value selected in the combo box into the table but it throws me this error that the value you entered is not valid for the Field 'ID'
but i tried my best it is pointing the ID of the vendor table.
Also code you asked looks for onchange combox is
Dim vendor As Variant
Dim strRQL As String
Dim str2 As Integer
Dim num As Integer
num = DCount("*", "Master")
If (Form_Master.ID <= num) Then
vendor = DLookup("vendor_name", "vendortable", "ID=" & Combo683.Value & "")
Set dbs = CurrentDb()
str2 = Form_Master.ID
strRQL = "Update Master set vendor="
strRQL = strRQL & " '" & vendor & "' WHERE ID=" & str2 & ";"

CurrentDb.Execute strRQL
dbs.Close

That error is poped when the the lines of code is executed but as soon as when i press the next button its shows that error. but my update was made into the table still shows the error i wonder why
Also if i click the refresh all button which is on top then the error doesn't pop weird

If the combo box is bound to the table you don't need the procedure you are showing. Any change to the box will be kept when you save the record. Besides the OnChange event is wrong one to use. You are creating unnecessary problems for yourself.

Best,
Jiri
 

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
i removed the on change event but it is not saving automatically
 

Mark_

Longboard on the internet
Local time
Today, 08:48
Joined
Sep 12, 2017
Messages
2,111
i removed the on change event but it is not saving automatically

Is the field bound to the control and the table bound to the form?

As you posted "Combo683.Value" I am guessing this is an unbound control. Please make sure that a field from your table is in the control source for this field.

I would also rename it so it has a meaningful name. Combo683 gives no meaning for what the control is supposed to do.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
42,970
There is no code required to save data in a bound control. If you change the value, Access automatically saves the change unless you cancel the update.

Try removing (or commenting out) all your form code and you'll see that the change saves as long as the control is bound.

A bound control DOES NOT start with "=". A bound control contains the name of a column from the RecordSource of the form.
 

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
Thank you all, problem solved actually it was automatically saving the data. but i placed a validation condition in the click event of save button thats why it was not saving automatically.

i have commented the validation then it works smoothly. after that i had two issues.

1. i have so many text boxes in the form master how can i validate it.

(if i use beforeupdate then the user must click on the textbox then only it can be validated. if keept after update also similar. if user doesnt even click on any textbox and presses save button it should ask for proper input data.)

2.it automatically saves the changed value from the combo box but it is storing a id number instead of text value.
Example:1 perry,2 yaws,3 not applicable
if i select perry then it save in the table as perry (when viewed in table)
but when i kept a alert in vba code it gives me value 1.
 

Mark_

Longboard on the internet
Local time
Today, 08:48
Joined
Sep 12, 2017
Messages
2,111
Thank you all, problem solved actually it was automatically saving the data. but i placed a validation condition in the click event of save button thats why it was not saving automatically.

i have commented the validation then it works smoothly. after that i had two issues.

1. i have so many text boxes in the form master how can i validate it.

(if i use beforeupdate then the user must click on the textbox then only it can be validated. if keept after update also similar. if user doesnt even click on any textbox and presses save button it should ask for proper input data.)

2.it automatically saves the changed value from the combo box but it is storing a id number instead of text value.
Example:1 perry,2 yaws,3 not applicable
if i select perry then it save in the table as perry (when viewed in table)
but when i kept a alert in vba code it gives me value 1.

For 2, you put the lookup into your table definition so ACCESS is doing the lookup and showing what you request when viewing the table. What is really stored is the ID.
 

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
thanks mark. but when i open the table it shows perry name itself but when i write code in vba it takes 1 as value. so clearly stores ID so my question was why it is not showing 1 as in raw table data ?? why is it showing Perry ??
 

Mark_

Longboard on the internet
Local time
Today, 08:48
Joined
Sep 12, 2017
Messages
2,111
In ACCESS, open the table in Design View.
Go down to your Vendor field.
Click on the Lookup tab. under field properties.

You should see Combo Box in the Display Control and information in Row Source Type. This is why your table is showing the vendor's name instead of the ID when viewing the table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
42,970
it automatically saves the changed value from the combo box but it is storing a id number instead of text value.
That is what it is supposed to do. Do NOT use table level lookups. Use combos on forms to allow the user to pick. The combo will be set with the "ID" column width set to 0 to hide the ID field and show the text field. Users NEVER directly access tables or queries so table level lookups are crutches for YOU and you, as the developer shouldn't need them. Just create a query if you want to see both the ID and text string.

The majority of your validation code belongs in the FORM's BeforeUpdate event. Rarely, you might use a CONTROL's BeforeUpdate event but when you do that you almost always need to duplicate the validation in the form level event anyway and one place is better than two as far as code goes. Your SAVE button should NEVER do validation. It should only force the record to be saved using
DoCmd.RunCommand acCmdSaveRecord

In the FORM's BeforeUpdate event, you cancel the save whenever you encounter an error. For example.
Code:
If IsDate(Me.txtDOB) Then
    If Me.txtDOB > Date() Then
        Msgbox "Date of Birth cannot be in the future.", vbOKOnly
        Me.txtDOB.SetFocus
        Cancel = True
        Exit Sub
    End If
Else
    Msgbox "Date of Birth is required.",vbOKOnly
    Me.txtDOB.SetFocus
    Cancel = True
    Exit Sub
End If
If Me.txtCompanyName & "" Then
    Msgbox "Company Name is required.",vbOKOnly
    Me.txtCompanyName.SetFocus
    Cancel = True
    Exit Sub
End If
 

sree0009

Registered User.
Local time
Today, 08:48
Joined
Nov 20, 2017
Messages
15
hi pat, this indeed helps. but i tried and it didn't work out for me. when i pressed the save button it saves the record and doesn't check for validation
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
42,970
Then your code is NOT in the correct event or you have not implemented the suggestion correctly.

If you don't post the code, there is no way for me to determine were the problem lies.
 

Users who are viewing this thread

Top Bottom