Frustrating coding

[penfold1992]

Hello, Im a little bit sad and frustrated that I cannot figure out what the problem is by myself but i got to my limit so ill post here hoping to get some help

I have a form which links to a bunch of different tables for various reasons but one of them is called "Suppliers" (theres a drop down menu to select an option in suppliers)

Im set to make another form that allows you to add another supplier so I have set up the table and the form and just need to code the form to get it to do what I want it to.
Everything seems to be working apart from the important button that Adds whatever you entered in a textbox into "Suppliers" table

Private Sub btnaddsupplier_Click()
On Error GoTo Err_btnaddsupplier_Click
Dim dbs As DAO.Database
Dim NewSuppl As String
Dim InsertSQL
Set dbs = CurrentDb

If (IsNull(NewSupplier.Value) = False) And (NewSupplier <> "") Then
    NewSuppl = NewSupplier.Value
Else
    MsgBox "New Supplier Box cannot be blank."
End If
InsertSQL = "UPDATE [Suppliers] set " + _
        "[Suppliers].[Suppliers] = '" + NewSuppl + "',"
dbs.Execute InsertSQL
Exit_Supplier_Click:
    Exit Sub
Err_btnaddsupplier_Click:
    Resume Exit_Supplier_Click
    MsgBox Err.Description
End Sub

Im not sure where the problem is or why I cannot spot it but maybe someone with a clearer eye can give me a solution and point out what ive done wrong here.

thanks in advanced! (ps when you type nothing in the box, i do get the messagebox popup)

[orange]

I have a form which links to a bunch of different tables for various reasons

Perhaps you could tell us more about your application. What is it you are doing? What are the things involvd? What Tables do you have?
As for this specific code you have shown, do you get an error message?
Do you have Lookups at the table level?

Can you show us some sample data and walk us through what you think should happen to that data in your procedure?

What is the purpose of this ?

InsertSQL = "UPDATE [Suppliers] set " + _ "[Suppliers].[Suppliers] = '" + NewSuppl + "',"

Before executing the SQL, you may wish to use a Debug.Print to see what is rendered.

[John_G]

Hi -

I addition to what Orange has asked -

If your code is supposed to ADD a record to the [Suppliers] table, then you need to use INSERT, not UPDATE.

If it is supposed to update a record, then you need a WHERE clause to tell it which record to update, otherwise it will update all of them with the same information.

Please give us a bit more information.

John

[penfold1992]

Apologies for the broad question, I overlooked lots it seems
I cannot supply screenshots or database files due to legal reasons however i will elaborate more.

There is a form that a user fills in which creates a new record with a variety of information in multiple tables. One of these questions a user needs to fill in is a drop down menu which contains a current list of suppliers. I have it set up so that this form looks into the table "Suppliers" and for the user to select one of these.
If the user finds the supplier is not on this list, they have the opertunity to add one through a form.
This form has a list of the current suppliers (for reference for the user) and then a box below that called "NewSupplier" this is where they can enter in whatever the new suppliers name is.
Below that are 2 buttons, one is Cancel and the other is "Add Supplier" (named btnaddsupplier)

what I need to happen is that when the button "Add Supplier" is pressed, the name that they have entered in the NewSupplier box gets inserted into the table "Suppliers"

so the form I am creating is not supposed to create a new record but more like, create a new option for someone who is creating a record.

[orange]

Show us the code behind the button event.

Also, you Update or Insert into 1 table at a time. Please describe your "creates a new record with a variety of information in multiple tables".

[penfold1992]

Show us the code behind the button event.

Also, you Update or Insert into 1 table at a time. Please describe your "creates a new record with a variety of information in multiple tables".

after reviewing the code it looks like... On launch the form requests the last record number and adds 1. So when pressing the buttons on the original form, the form updates one table at a time with the relevent information that the form supplies.

the new form I am creating does not care about the record number or any previous records at all other then the "record" in the table "Suppliers" which needs to be seperate from the others. So the normal form will hold all the recorded information (like a regular incident database adding 1 to the last incident number) whilst the new Suppliers form just needs to add more records into the Suppliers table alone without effecting anything else.

once the new supplier is added to the suppliers table though it should appear as a selectable in the other form.

If you create a new drop down box and you just select its row source as some text values then allow value list edits, it will bring up a "Edit List Items" box in which you can add or remove to the options, the form is supposed to be a form of this but the options are stored in a table.

as for the button event:

Private Sub btnaddsupplier_Click()
On Error GoTo Err_btnaddsupplier_Click
Dim dbs As DAO.Database
Dim NewSuppl As String
Dim InsertSQL
Set dbs = CurrentDb

If (IsNull(NewSupplier.Value) = False) And (NewSupplier <> "") Then
    NewSuppl = NewSupplier.Value
Else
    MsgBox "New Supplier Box cannot be blank."
End If
InsertSQL = "INSERT INTO [Suppliers] VALUES ( "", '" + NewSuppl + "')"
dbs.Execute InsertSQL

Exit_Supplier_Click:
    Exit Sub
Err_btnaddsupplier_Click:
    Resume Exit_Supplier_Click
    MsgBox Err.Description
End Sub

this is the current set up.