Frustrating forms code

[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)

 

[Isskint]

If you are wanting to add a new record you need to use INSERT INTO not UPDATE. Update will update a current value in the specified table

 

[penfold1992]

I have changed that to "INSERT INTO" however I am still not getting an update into the table and im not sure why.
in the table "Suppliers" i have 2 columns, one column is ID and the other is Supplier but i dont think i need the column "ID" it was just a number I could use to check what number a specific supplier was however it doesnt really need to be there i guess.

Also when i open up the form i get a bar at the bottom with the "Record" number which i do not need. I am not adding a new "record" so to speak, i am adding another option which the user can then enter on the record.

the user is to fill out a form and one of the options is to select the supplier from a drop down list. If the supplier is not in that list they can create a new one by using another form which is the one I am creating.

the "record" form has been changed to select a supplier from a drop down list which is populated by looking at the all the options in the "Supplier" table.

i hope I gave a little more information here.

 

[Isskint]

If the INSERT INTO SQL is not inserting a new record you must have an error message detailing why. What is that error message?

On second reading of your issue it seems you simply want to add a new supplier to the suppliers table. Is this the case? If so just set up a pop up form with the control source to the suppliers table, for data entry. That way when a new supplier needs to be added, you just open the form, type the new supplier (refresh/requery the original form) and hey presto, all done.

 

[penfold1992]

On second reading of your issue it seems you simply want to add a new supplier to the suppliers table. Is this the case? If so just set up a pop up form with the control source to the suppliers table, for data entry. That way when a new supplier needs to be added, you just open the form, type the new supplier (refresh/requery the original form) and hey presto, all done.

this is very similar to what i need to do however I am so new with access i dont really understand how to do that...
If i can get what you are describing to work, I can modify it in order to also show the current list of suppliers as well.
is it possible to explain how I can do this? sorry for the beginner like qualities.

 

[Isskint]

Ok, lets start again

So, create a form, just as normal, using the form wizard. Select the suppliers table as the Control Source. set it up to look how ever you want. Save it. Now open it in design view and goto Properties>>Data and set Data Entry to Yes. This means you can now only enter new data through this form.
Now Properties>>Format and set the following;
Scroll Bars = Neither - optional
Record Selectors = No - optional
Navigation Buttons = No - optional
Auto Resize = No - optional
Auto Center = Yes - optional
Border Style = Dialog

Now Properties>>Other and set the following;
Pop Up = Yes
Modal = Yes

This will give you a Pop Up form that can be presented to the user to input a new supplier. To call this form, add a command button to your current form to open this new form.

You could add a command button to the new form to close it (it would allow for multiple new suppliers OR add some VBA to the form to close after the new supplier is added)

 

[penfold1992]

This helps in some sense.
This box now creates records however there are some issues i have with it...
firstly if i create a new supplier it adds 1 to the last ID. I currently have 9 test items in the list and if I add 1 it will become the 10th ID. If i then delete that 10th id and recreate it, it then becomes 11th ID, missing out number 10. I dont exactly know what the fix is for this though...

secondly, i was hoping I could view what this was exactly doing in VB (in order to try to copy and manipulate the code but there is just no code at all.

thirdly, I was hoping to get a "Confirm" button instead of just being able to hit enter, this will reduce the number of spelling mistakes and accidental entrys.

 

[penfold1992]

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 (Suppliers) VALUES ( '" & NewSuppl & "')"
dbs.Execute InsertSQL
Debug.Print "Successful Insertion"
Exit_Supplier_Click:
    Exit Sub
Err_btnaddsupplier_Click:
    Resume Exit_Supplier_Click
    MsgBox Err.Description
End Sub

This is a WORKING BUTTON!!!! FINALLY!

there are a few questions i have about it though.
Firstly is the ID issue I listed in my previous post.
Secondly, is there a way I can open a message box saying "Are you sure you wish to add the supplier (NAMEINSUPPLIERBOX)"
also.... this updates the supplierlist

SupplierList.Requery

however thats only if the "SupplierList" is on the form. I also need to update the original form to include the added option into the combo box in the other form. Is that possible? maybe an EventOnClose?