Solved Trying to simply add to a table instantly from a form (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
Hi,

I have tried all kinds of things and searched and tried more but i know i am missing something somewhere?

I have an input form with a subform and when an item is not in a combobox list i have a button on the form which opens a form for the user to enter an item for inclusion on the combobox list.

It works but when they return to the form, the item is not displayed in the combobox list until the form is closed and opened again.

I need it to be instant so the user can continue to add data to the form and record.

I tried the Requery method but that sent the data to the table incomplete as did the Refresh method.

So i am still trying to figure the way out.

Many thanks in advance
 

tmyers

Well-known member
Local time
Today, 12:01
Joined
Sep 8, 2020
Messages
1,090
The method I used for this situation is to use the comboboxes Not in List event.
The code that I used was :
Code:
Dim strtmp As String
    strtmp = "Add '" & NewData & "' enter your message here"
    If MsgBox(strtmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        strtmp = "INSERT INTO [YourTable] ( [Yourfield]"
        strtmp = strtmp & "VALUES(""" & NewData & """, " & Forms("[Yourformname]").[YourPK] & ");"
        DBEngine(0)(0).Execute strtmp, dbFailOnError
       
        Response = acDataErrAdded
    End If
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:01
Joined
May 21, 2018
Messages
8,463
You need to open the pop up in ACDIALOG. This will stop the code execution until you close the pop up.

Docmd.openform "yourPop up",,,, ACdialog
me.mycombo.requery

if you do not open acdialog the requery happens immediately as soon as the pop up opens and no changes have been made.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:01
Joined
Oct 29, 2018
Messages
21,358
Hi. If you want to continue using your button, you could open the other form in Dialog mode and then requery the combobox.

Edit: Oops, too slow. Sorry for the duplicate info...
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
The method I used for this situation is to use the comboboxes Not in List event.
The code that I used was :
Code:
Dim strtmp As String
    strtmp = "Add '" & NewData & "' enter your message here"
    If MsgBox(strtmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
  
        strtmp = "INSERT INTO [YourTable] ( [Yourfield]"
        strtmp = strtmp & "VALUES(""" & NewData & """, " & Forms("[Yourformname]").[YourPK] & ");"
        DBEngine(0)(0).Execute strtmp, dbFailOnError
      
        Response = acDataErrAdded
    End If
Thank you guys.

I just tried this but getting errors and not 100% what i am doing with it? It nearly working:

Private Sub WLL_NotInList(NewData As String, Response As Integer)

Dim strtmp As String
strtmp = "Add '" & NewData & "' Add a new WLL"
If MsgBox(strtmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

strtmp = "INSERT INTO [WLL] ( [WLL]"
strtmp = strtmp & "VALUES(""" & NewData & """, " & Forms("EnterItem").ExamStatusSubform.WLL & ");"
DBEngine(0)(0).Execute strtmp, dbFailOnError

Response = acDataErrAdded
End If

End Sub
 

tmyers

Well-known member
Local time
Today, 12:01
Joined
Sep 8, 2020
Messages
1,090
Thank you guys.

I just tried this but getting errors and not 100% what i am doing with it? It nearly working:

Private Sub WLL_NotInList(NewData As String, Response As Integer)

Dim strtmp As String
strtmp = "Add '" & NewData & "' Add a new WLL"
If MsgBox(strtmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

Code:
strtmp = "INSERT INTO [WLL] ( [WLL]"
strtmp = strtmp & "VALUES(""" & NewData & """, " & Forms("EnterItem").ExamStatusSubform.WLL & ");"
DBEngine(0)(0).Execute strtmp, dbFailOnError

Response = acDataErrAdded
End If

End Sub
Looks like I missed a closing parenthesis in one spot. Must have done it while editing out my field data. On your line:
Code:
 strtmp = "INSERT INTO [WLL] ( [WLL]"
It should be:
Code:
 strtmp = "INSERT INTO [WLL] ( [WLL] )"

Beyond that, I defer to a much more versed person than I. I just happened to have dealt with a very similar ordeal. If adding that missing parenthesis doesn't fix it, please let us know what the exact error you are getting is.
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
Still producing the errors as in the attached screenshots?
 

Attachments

  • Screenshot 2020-12-10 170511.png
    Screenshot 2020-12-10 170511.png
    5 KB · Views: 111
  • Screenshot 2020-12-10 170554.png
    Screenshot 2020-12-10 170554.png
    31 KB · Views: 98

mike60smart

Registered User.
Local time
Today, 16:01
Joined
Aug 6, 2017
Messages
1,899
Hi Sam
You can use the On Enter Event of the Combobox.

You just use :- Me.NameOfCombobox.requery
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:01
Joined
May 21, 2018
Messages
8,463
if that is not compiling you need "'" not """, double-single-double
Once it compiles
before execute
debug.print strTmp
post the string back here.

SQL:
strtmp = "INSERT INTO [WLL] ( [WLL ]" ' add a space here
strtmp = strtmp & "VALUES("'" & NewData & "'", " & Forms("EnterItem").ExamStatusSubform.WLL & ");"
debug.print strTmp
DBEngine(0)(0).Execute strtmp, dbFailOnError
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
Good morning guys,

Thought i had it but still getting the messages as attached?
 

Attachments

  • Screenshot 2020-12-11 083712.png
    Screenshot 2020-12-11 083712.png
    5.1 KB · Views: 113
  • Screenshot 2020-12-11 083745.png
    Screenshot 2020-12-11 083745.png
    30.8 KB · Views: 103

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
14,044
You need to show what the Debug.Print displays?
That was the whole idea of adding that, so you can see where the error(s) are?
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
The only message i am getting now are these?
 

Attachments

  • Screenshot 2020-12-11 092152.png
    Screenshot 2020-12-11 092152.png
    5 KB · Views: 110
  • Screenshot 2020-12-11 092226.png
    Screenshot 2020-12-11 092226.png
    32.2 KB · Views: 110

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
14,044
Either use View/Immediate Window or ctrl+G to open it.

There you will see the results of any Debug.Print statement.

It is that which you need to copy and paste here
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
Thank you so much for explaining.

I need to take a look at the WLL part again and try and figure out why it is failing?
 

Attachments

  • Screenshot 2020-12-11 100123.png
    Screenshot 2020-12-11 100123.png
    37.5 KB · Views: 102

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
14,044
You will need at least single quotes ' around any string and # around dates and nothing around numerics.
Perhaps as MajP posted?

COPY that as he has the single quotes in his strtmp
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
Ok, maybe its the data that is causing the error?

The data examples are - 6.5T or 2te or 5000Kg and so on
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
14,044
Ok, maybe its the data that is causing the error?

The data examples are - 6.5T or 2te or 5000Kg and so on
Well they are all strings?, so MajP's code should work. You have not used his code?
 

Sam Summers

Registered User.
Local time
Today, 16:01
Joined
Sep 17, 2001
Messages
939
I haven't a clue whats going on? I'll just try and figure it out. Thank you for your help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:01
Joined
May 7, 2009
Messages
19,169
Code:
strtmp = "INSERT INTO WLL (WLL) "
strtmp = strtmp & "VALUES ('" & NewData & "'," & [Forms]![EnterItem]![ExamStatusSubform].Form![WLL] & ");"
 

Users who are viewing this thread

Top Bottom