Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2016
    Posts
    5

    Unanswered: SQl / VBA referencing help

    I am having trouble creating a particular Sub within Access. I am Attempting to create a button on a form which will append a new record to and existing table based upon parameters set by the user. I am teaching myself as I create this database so my knowledge is limited to begin with.

    I am experiencing the error :

    Runtime error 2465

    Application defined or object defined error

    I expect this has to do with referencing the wrong things or not defining things properly but I don't know where to start

    My current code looks like this

    Code:
    Private Sub cmdMakePair_Click()
    'set things up'
    Dim dbs As Database
    Set dbs = CurrentDb()
    Dim NF As String
    Dim NM As String
    Dim NG As String
    'refresh the txtboxs im pulling values from as a workaround (because I dont know how to pull specific values from a query)'
    Me.TXTNFGeno.Requery
    Me.TXTNMGeno.Requery
    
    
    'add the two txt values to create a new value'
    NG = "Me.TXTNMGeno + Me.TXTNFGeno"
    'if either of the combo boxes are empty show an error msg' 'if they contin something then set values of NM and NF'
    If (IsNull(Form.Make_a_Breed_Pair.cmboDesiredMale = 0)) Then
    NM = (Form.Make_a_Breed_Pair.cmboDesiredMale)
    Else
    MsgBox ("Your must select a male parent for the breeder pair")
    End
    End If
    If (IsNull(Form.Make_a_Breed_Pair.cmboDesiredFemale = 0)) Then
    NF = (Form.Make_a_Breed_Pair.cmboDesiredFemale)
    Else
    MsgBox ("You must select a Female parent for the breeder pair")
    End
    
    End If
    'insert the values into an existing table'
    dbs.Execute "INSERT INTO Breeder Mice [(Breeder ID, Strain)] VALUES (NM + NF, NG)"
    
    End Sub
    Any help would be appreciated.

    Thanks in advance,

    o3smog

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The line that throws the error is?

    My immediate suspiscion is that its the penultimate line.
    Code:
    dbs.Execute "INSERT INTO Breeder Mice [(Breeder ID, Strain)] VALUES (NM + NF, NG)"
    And that is caused because you are not 'escaping' to vba variables.
    Instead try :-
    Code:
    ........VALUES (" & NM + NF & ", " & NG & ")"
    I always recommend that whenver interacting with a sql database assign the sql statement to a variable then use the variable, rather than the style you have used. The reason ?
    You can examine what you are intending to send by looking at the valye of the variable. Eg :-
    Code:
    DIM strSQL AS STRING
    strSQL = "SELECT my, column, list FROM mytable WHERE acolumn = " & avalue & ";"
    Dbs.execute strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    also look at the line

    NG = "Me.TXTNMGeno + Me.TXTNFGeno"


    should it be

    NG = Me.TXTNMGeno + Me.TXTNFGeno
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jul 2016
    Posts
    5
    This is the line that is showing an error:
    Code:
    If (IsNull(Form.Make_a_Breed_Pair.cmboDesiredMale = 0)) Then
    Thanks for the styling suggestion healdem I will be working that into the way I code from now on.

  5. #5
    Join Date
    Jul 2016
    Posts
    5

    I have done a little tweaking

    I have cleaned some stuff up but it is telling me i have a syntax error in my INSERT INTO

    I havn't been able to put my finger on it

    Code:
    Option Compare Database
    
    Private Sub cmdMakePair_Click()
    Dim db As DAO.Database
    Set db = CurrentDb()
    Dim NF As String
    Dim NM As String
    Dim NG As String
    Dim DesMale As Object
    Set DesMale = Forms![Make a Breed Pair]![cmboDesiredMale]
    Dim DesFemale As Object
    Set DesFemale = Forms![Make a Breed Pair]![cmboDesiredFemale]
    Dim strINSERT As String
    
    strINSERT = "INSERT INTO Breeder Mice (Breeder ID,Strain)VALUES(DesMale+DesFemale,NG);"
    Me.TXTNFGeno.Requery
    Me.TXTNMGeno.Requery
    
    
    
     NG = "Forms.[Make a Breed Pair]![TXTNMGeno] + Forms.[Make a Breed Pair]![TXTNFGeno]"
    
        If DesMale = Null Then
             MsgBox ("Your must select a male parent for the breeder pair")
             End
                Else
                    If DesFemale = Null Then
                        MsgBox ("You must select a Female parent for the breeder pair")
                        End
                Else
    CurrentDb.Execute strINSERT
    
                    
    End If
        
    End If
    
    
    End Sub

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    See post #2
    You are not picking up the values of the variables, but instead supplying the variable names in place. Because the SQL engine expects alpha values to be delimuted its whining about a syntax error.

    Examine the value of the variable strInsert and make certain its what you think it should be
    Last edited by healdem; 07-23-16 at 04:40.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Also if you use a space in a column or table name it myst be delimited in square brackets. Eg
    [Breeder id]
    That us a klunky workaround, the fix is not to use reserved wirds ir symbols in the first place. When naming tables or columns that are comprised of mire than one word either use CamelCase or underscores. Eg
    BreederId
    OR
    breeder_id

    All lowercase, separated by underscores is probably the preferred approach as its consistent accrooss vurtually all SQL databases and operating systems.

    Use a consistent naming convention that uses similar terms for similar columns, and uses a consistent set of abbreviations eg tel_no in place of telephone_number
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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