Results 1 to 3 of 3

Thread: SQL statement

  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Question Unanswered: SQL statement

    I was given an SQL statement to use in my NotInList Combo Box. It assigns my BillToID number to 0(Zero) so, It works great 1 time. However, because I do not use an Auto Number the next time it does the code it looks for 0 once again. I use a macro with a Dmax +1 statement to give me the next number in line. Since I am going through Sql, is there a way to enter my new data in the next sequential order?

    I am new to this coding stuff so go easy on me...

    Here is the code I am using:
    Private Sub BillToID_NotInList(NewData As String, Response As Integer)
    Response = acDataErrContinue
    Dim msg As String
    Dim strSQL As String
    Dim strSQLB As String
    msg = NewData & " not in the database!" & vbCrLf & vbCrLf
    msg = msg & "Create a new entry?"
    If MsgBox(msg, vbQuestion + vbOKCancel, NewData & " not in list!") = vbCancel Then
    BillToID = Null
    Else
    strSQL = "INSERT INTO TblBillTo (Customer) VALUES ('" & NewData & "');"
    DoCmd.RunSQL (strSQL)
    Response = acDataErrAdded
    BillToID.Undo
    BillToID.Requery
    End If
    End Sub
    Gotta to do some code

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    if NewData returns the next number, should you not store that or at least assign it to billToID:

    BillToID = newData
    strSQL = "INSERT INTO TblBillTo (Customer) VALUES ('" & BillToID & "');"
    DoCmd.RunSQL (strSQL)
    Response = acDataErrAdded

    or a local variable could be used if you do not want to touch BillToID. Once it is added, do you not want it to be set to the new id?

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    NO

    When I go to the Bill To Table The BilltoID is automatically assigned using a set value w/ Dmax +1 expression. So I am updating actually the next field of the form... the customer field
    Gotta to do some code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •