how to update table

[questionit]

I have a text field on a Form, which is linked with table.

If i input some value in the text field, how would i update the table with the inputted value - without closing the form ?

I have tried using Me.Requery but it dosn't append the new value to the old ones but replaces the old value.

Any help?

[vkong85]

you can run the textbox off a append query so everytime you requery it appends it onto the table.

[questionit]

Can you tell me how to do it?

you can run the textbox off a append query so everytime you requery it appends it onto the table.

[missinglinq]

There's no need to run an append query for this simple task! You just need to Save the record for it to be written to your table. Assuming that all required fields have data, if you move to another record, the record will be Saved. If you close the form, the record will be Saved! You should always get a warning if you move to another record or go to close the form if any required fields are empty, but this doesn't always happen when you go to close the form! Sometimes Access simply dumps the incomplete record, without any warning! You can also, at any time, explicitly Save the record, either with

Docmd.RunCommand acCmdSaveRecord

or with:

If Me.Dirty Then Me.Dirty = False

Linq ;0)>

[questionit]

Linq,

Both the methods you have told me does not append new values to record. It still replaces the old value.

How would you add new record to the table?

There's no need to run an append query for this simple task! You just need to Save the record for it to be written to your table. Assuming that all required fields have data, if you move to another record, the record will be Saved. If you close the form, the record will be Saved! You should always get a warning if you move to another record or go to close the form if any required fields are empty, but this doesn't always happen when you go to close the form! Sometimes Access simply dumps the incomplete record, without any warning! You can also, at any time, explicitly Save the record, either with

Docmd.RunCommand acCmdSaveRecord

or with:

If Me.Dirty Then Me.Dirty = False

Linq ;0)>

[hyperpau]

Linq,

Both the methods you have told me does not append new values to record. It still replaces the old value.

How would you add new record to the table?

The confusion is here is caused by your use of the "Append" word.
when you say append, we normally understand it as replacing the old value.
So if I understand it correctly, you want to add a new value in the combo box right? meaning, the old value will stay unchanged, and the new value becomes a new choice in the list?

you just need to requery. But requery only that field and not the whole form.

this are my suggestions.

That combo box field you have there, format it as limit to list = Yes
so that if a user types a new data which is not yet in the list, they would not be able to change or append that old value currently selected.

then create an unboun form where a user can add a new value to the list.

details:

(assuming your combo box is bound to a table that contains the choices in the combobox)

add an 'On DblClick' and 'On Not in List' event procedure to that combo box field.
let's say your combo box field is named [comboDescription]
add a vba code to the OnNotinList event.

Private Sub comboDescription_NotInList(NewData As String, Response As Integer)
    MsgBox "Text you entered is not in the List" & _
            vbCrLf & "Double Click to Add a new Descripton.", , "Error!"
    Response = DataErrCont
End Sub

when a user types in a new value in the combo box which is not yet on the list of choices (not yet in the table where the combo box is bound to), then
access would prompt the user saying that what they typed in is not in the list.
Then that message box would also tell them that to add that value, they must
double click the combo box.

Now, you create an unbound form to let the user add a new description in the combobox.

This unbound form would have a textbox bound to the descriptions table, then two command buttons. one for add/save, and one for cancel.
Have this unbound form's property set to Date Entry = Yes

Then the 1st command button, let's say an "Add" button should command
access to save whatever the user types in the text box to the table of the descriptions.

So the OnClick event procedure of the add button is something like this:

(Let's say the orginal form is named "frmProducts" and the unbound form is named "frmAddDescription")

Private Sub cmdAdd_Click()
    If IsNull(Me![Description]) Then
        MsgBox "There is no Description to Add." & _
        vbCrLf & "Please click Cancel to Close the Form.", _
        vbInformation, "Add a Description"
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close acForm, "frmAddDescription", acSaveYes
    Forms![frmProducts]![comboDescription].Requery
    End If
End Sub

The other button in the unbound form is the cancel button that just closes
the unbound form. you may use the wizard for this.


Now lets go back to the original form "frmProducts".
After adding that NotInList event.
we will now add the "On Dbl Click" event.

Private Sub comboDescription_DblClick(Cancel As Integer)
    Me![comboDescription] = ""
    DoCmd.OpenForm "frmDescription"
End Sub

There you go, your issue is resolved. The scenario would be like this.

The user opens your form 'frmProducts' then input information, now
the user needs to select a description of the product from the combobox
'comboDescription'.
Unfortunately, the combo box does not have the description the user is looking for for this product. therefore he types a new one in the combobox.
since the description he typed is not in the list, a message box appears
saying "Text you entered is not in the list. Double click to add a new description"


so the user clicks ok on the message box then of course, follows what the message box said. The user double clicks the combo box.

This would open the unbound form 'frmAddDescription' without closing the current form.

now on the unbound form, the user types that new description in the text box and clicks the "Add" button.
this closes the unbound form and the focus goes back to the original form.
When the user click the down arrow of the combo box, he will now see the
new description he entered in the unbound form and selects that. :)

Hope this would help.

[missinglinq]

hyperpau
So if I understand it correctly, you want to add a new value in the combo box right?

Where in this post is a combobox ever mentioned?

questionitHow would you add new record to the table?

To add a new record to a table you have to first move to a new record then enter your data. It now sounds like you're trying to enter data in an existing record and then expect it to be saved to a new record!

To go to a new record either

Click on the far right button in the records navigation box (the one with an asterisk next to it or place a button on your form with this code behind it:

DoCmd.GoToRecord , , acNewRec
 

.

[questionit]

Thanks a lot for your details help.

but what i was asking was the method to save a value in a table.

I have a TextBox, i type any value in it. This value should store in already existing table that has been linked with the Text Field.

There are already some values in the table, so the new value should tore in the end (by adding a new row)

how would i do that..... i know when we do Me.Requery kind of things, the new value gets stored in table - but this way the old and the most reent value gets replaced - but i want to add a new row?

The confusion is here is caused by your use of the "Append" word.
when you say append, we normally understand it as replacing the old value.
So if I understand it correctly, you want to add a new value in the combo box right? meaning, the old value will stay unchanged, and the new value becomes a new choice in the list?

you just need to requery. But requery only that field and not the whole form.

this are my suggestions.

That combo box field you have there, format it as limit to list = Yes
so that if a user types a new data which is not yet in the list, they would not be able to change or append that old value currently selected.

then create an unboun form where a user can add a new value to the list.

details:

(assuming your combo box is bound to a table that contains the choices in the combobox)

add an 'On DblClick' and 'On Not in List' event procedure to that combo box field.
let's say your combo box field is named [comboDescription]
add a vba code to the OnNotinList event.

Private Sub comboDescription_NotInList(NewData As String, Response As Integer)
    MsgBox "Text you entered is not in the List" & _
            vbCrLf & "Double Click to Add a new Descripton.", , "Error!"
    Response = DataErrCont
End Sub

when a user types in a new value in the combo box which is not yet on the list of choices (not yet in the table where the combo box is bound to), then
access would prompt the user saying that what they typed in is not in the list.
Then that message box would also tell them that to add that value, they must
double click the combo box.

Now, you create an unbound form to let the user add a new description in the combobox.

This unbound form would have a textbox bound to the descriptions table, then two command buttons. one for add/save, and one for cancel.
Have this unbound form's property set to Date Entry = Yes

Then the 1st command button, let's say an "Add" button should command
access to save whatever the user types in the text box to the table of the descriptions.

So the OnClick event procedure of the add button is something like this:

(Let's say the orginal form is named "frmProducts" and the unbound form is named "frmAddDescription")

Private Sub cmdAdd_Click()
    If IsNull(Me![Description]) Then
        MsgBox "There is no Description to Add." & _
        vbCrLf & "Please click Cancel to Close the Form.", _
        vbInformation, "Add a Description"
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close acForm, "frmAddDescription", acSaveYes
    Forms![frmProducts]![comboDescription].Requery
    End If
End Sub

The other button in the unbound form is the cancel button that just closes
the unbound form. you may use the wizard for this.


Now lets go back to the original form "frmProducts".
After adding that NotInList event.
we will now add the "On Dbl Click" event.

Private Sub comboDescription_DblClick(Cancel As Integer)
    Me![comboDescription] = ""
    DoCmd.OpenForm "frmDescription"
End Sub

There you go, your issue is resolved. The scenario would be like this.

The user opens your form 'frmProducts' then input information, now
the user needs to select a description of the product from the combobox
'comboDescription'.
Unfortunately, the combo box does not have the description the user is looking for for this product. therefore he types a new one in the combobox.
since the description he typed is not in the list, a message box appears
saying "Text you entered is not in the list. Double click to add a new description"


so the user clicks ok on the message box then of course, follows what the message box said. The user double clicks the combo box.

This would open the unbound form 'frmAddDescription' without closing the current form.

now on the unbound form, the user types that new description in the text box and clicks the "Add" button.
this closes the unbound form and the focus goes back to the original form.
When the user click the down arrow of the combo box, he will now see the
new description he entered in the unbound form and selects that. :)

Hope this would help.

[missinglinq]

Post # 7 explains how to do this! You have to go to a new record first, then enter data in your text field! Then save it to your table! You cannot enter data into a text box on an existing record and expect it to be appended to the table as a new record!

[hyperpau]

Thanks a lot for your details help.

but what i was asking was the method to save a value in a table.

I have a TextBox, i type any value in it. This value should store in already existing table that has been linked with the Text Field.

There are already some values in the table, so the new value should tore in the end (by adding a new row)

how would i do that..... i know when we do Me.Requery kind of things, the new value gets stored in table - but this way the old and the most reent value gets replaced - but i want to add a new row?

That answers your question. If you just want to add a new data in the table where the text box is bound, but not enter a whole new record, then you have to change your textbox to a combo box. On the design view, right click the text box and change it to combo box.

But if what you're talking about is to add a whole new record with that new value for the textbox, thene missinling gave you the answer already.