the value you entered is not valid for this field

[sreee0009]

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.

[June7]

Having difficulty making sense of your narrative.

Vendor is a text type field?

Why would you need to use an UPDATE statement? Provide the SQL syntax.

I have NEVER used OnChange event for textboxes or comboboxes.

[sreee0009]

yes vendor is short text, i can save the record normally when i select the value from the combo box because my combox is bounded to vendor field. but also i have created a macro for a button which is next to go for other record. but while viewing other records using next button i can view all the data from the master table.
the issue is if a user wants the value to change from one to another in he combo box then the onchange event triggers and then the value should be updated by old value in the table
similar like this


also i pulled values from that vendor table to combo box.

[June7]

Why does that look like an Excel spreadsheet?

I still don't understand why you need UPDATE action. If form is bound to table and combobox is bound to field, data should save directly to record. I do not understand 'updated by old value' - what does old value have to do with populating vendor field in Master table?

You still haven't posted code.

[sreee0009]

im confused even what you understood is right or not ?
i have typed that table roughly in the excel and gave you for a look
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

[June7]

Still makes no sense to me. Combobox is bound to vendor field then it is already saving value into the field. User changes the value of vendor by selecting another item from the combobox.

What is the RowSource of the combobox? Is it an SQL statement? Post the SQL.

I don't use OnChange, I use AfterUpdate.

[sreee0009]

Thank you june7, 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 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.)

[June7]

I have never used the form BeforeUpdate and AfterUpdate events. I disable the form X close and use code in button Click event to validate input. I also use control ValidationRule and ValidationText properties.

[sreee0009]

still cant get you how to check for validation for different textboxes and their conditions,if empty. when they are not even selected

[June7]

Example:

Private Sub btnClose_Click()
    If IsNull(Me.tbxLabNum) Then
        MsgBox "Must enter value into LabNum"
        Me.tbxLabNum.SetFocus
    Else
        DoCmd.Close acForm, Me.Name, acSaveNo
    End If
End Sub

However, just setting the ValidationRule and ValidationText properties would accomplish the same thing for this particular example.