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

Sam Summers

Registered User.
Local time
Today, 13:05
Joined
Sep 17, 2001
Messages
939
Tried the above and now i am getting this in the Debug window:

INSERT INTO [WLL] ( [WLL] )VALUES(
INSERT INTO [WLL] ( [WLL] )VALUES(
INSERT INTO WLL (WLL) VALUES ('6.5T',);
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:05
Joined
Sep 21, 2011
Messages
14,052
Tried the above and now i am getting this in the Debug window:

INSERT INTO [WLL] ( [WLL] )VALUES(
INSERT INTO [WLL] ( [WLL] )VALUES(
INSERT INTO WLL (WLL) VALUES ('6.5T',);
So there is nothing in the form control.?

You need to make sure the data is valid before even attempting to insert it.
 

Sam Summers

Registered User.
Local time
Today, 13:05
Joined
Sep 17, 2001
Messages
939
That is the only messages coming up in the debug window?

All i am trying to do is to give the user the ability to add data to the combobox field as they are completing the form and then instantly select the data they have entered?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:05
Joined
Sep 21, 2011
Messages
14,052
The debug window merely adds to itself, so the first two lines are from your previous attempt and the third from your last attempt.
Delete them all if it is confusing you, so you only get to see the latest when testing.

Here is how I did it in one of my DBs. Just another way, due to my inexperience. Do not worry about the mixed_case function, that was just for my preference/use.

Code:
Private Sub Crew_ID_NotInList(NewData As String, Response As Integer)
    Dim txtSurname As String, txtInitials As String, strPKField As String
    Dim intNewID As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Response = acDataErrContinue

    If MsgBox(NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

        strSQL = "SELECT * from  Crew WHERE 1 = 0"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSQL)
        txtSurname = Left(mixed_case(NewData), InStr(1, NewData, " ") - 1)
        txtInitials = UCase(Trim(Mid(NewData, InStr(1, NewData, " ") + 1)))

        rs.AddNew
        rs!Surname = txtSurname
        rs!Initials = txtInitials
        strPKField = rs(0).Name                  'Find name of Primary Key (ID) Field
        rs.Update

        rs.Move 0, rs.LastModified
        intNewID = rs(strPKField)
       
        Response = acDataErrAdded
MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    Else
        Response = acDataErrDisplay
    End If
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom