Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Unanswered: Need help building a VBA script for my Form (siindho, need you!)

    ok, heres whats happening,

    when on my form, theres 3 important fields only worth mentioning

    Postcode
    Town
    Borough

    Postcode is a drop down box with 3 columns each displaying Postcode, Town, and Borough, so when you select a postcode it will automatically enter the "town" field and the "Borough" Field on the form with information related in a seperate Table "PostcodesTBL"

    heres the code that operates that (courtesy of Siindho)

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub ComboPostCodes_AfterUpdate()
    
        Me.Town1 = Me.ComboPostcodes.Column(1)
        Me.Borough1 = Me.ComboPostcodes.Column(2)
        
    End Sub
    
    Private Sub ComboPostCodes_Enter()
    
        Me.ComboPostcodes.Value = Null
        Me.ComboPostcodes.Dropdown
        
    End Sub
    Row Source is:
    Code:
     SELECT PostCodesTBL.PostCode, PostCodesTBL.Town, PostCodesTBL.Borough FROM PostCodesTBL ORDER BY PostCodesTBL.PostCode;
    now, i had ago at writing my own script, and heres what i wanted to acheive:

    If you type a postcode (say DA16) in the box, and it doesnt have that postcode in the Table "PostcodesTBL" then it prompts with a vbyesno box, asking if you want to add that postcode to the list, which in effect will populate "PostcodesTBL"

    So if you click Yes, then it brings up an Input box, (please specify a town to add to DA16) asking you to specify a town to go in the "town" field of the List And PostcodesTBL, you type a town, and it adds it to PostcodesTBL right next to the specified postcode, and then same thing for the borough, heres what ive written

    Code:
    Private Sub ComboPostcodes_NotInList(NewData As String, Response As Integer)
    
    Dim Twnar As String
    Dim Bhar As String
    Dim Pcar As String
    
        Pcar = "Add '" & NewData & "' as a new Postcode?"
        If MsgBox(Pcar, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then
        
            Pcar = "INSERT INTO PostcodesTBL ( Postcode ) " & _
            "SELECT """ & NewData & """ AS Postcode;"
            DBEngine(0)(0).Execute Pcar, dbFailOnError
            
            Response = acDataErrAdded
            
            
            Twnar = InputBox("Please enter a Town to associate """ & Pcar & """ with", "Enter Town")
            
                Twnar = "INSERT INTO PostcodesTBL ( Town ) " & _
                "SELECT """ & Twnar & """ AS Town;"
                DBEngine(0)(0).Execute Twnar, dbFailOnError
                
            
            Bhar = InputBox("Please Enter a Borough to associate with """ & Pcar & """  and """ & Twnar & """ ", "Enter Borough")
            
                Bhar = "INSERT INTO PostcodesTBL ( Borough ) " & _
                "SELECT """ & Bhar & """ AS Borough;"
                DBEngine(0)(0).Execute Bhar, dbFailOnError
                
                
            End If
            
    End Sub

    also i shall attach a copy of the database you can see for yourself the result (sooo close!)


    Thanks for your help everyone
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what form is does this code run on?

    incidentally your db will benefit from an occasional 'compact & repair'. but if you do choose to do a C&R make certain you take a copy first just in case the C&R goes pete tong
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider revising your code
    Code:
    Dim Twnar As String
    Dim Bhar As String
    Dim Pcar As String
    Dim strSQL As String
        Pcar = "Add '" & NewData & "' as a new Postcode?"
        If MsgBox(Pcar, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then
            Twnar = InputBox("Please enter a Town to associate """ & Pcar & """ with", "Enter Town")
            Bhar = InputBox("Please Enter a Borough to associate with """ & Pcar & """  and """ & Twnar & """ ", "Enter Borough")
         
                strSQL = "INSERT INTO PostcodesTBL ( Postcode, Town, Borough ) " & _
                " VALUES ('" & NewData & "', '" & Twnar & "', '" & Bhar & "');"
                'temp for testing
                MsgBox strSQL, vbInformation, "MySQL is:-"
                
                DBEngine(0)(0).Execute strSQL, dbFailOnError
                Response = acDataErrAdded 'hide the error message
    
            End If
    no to sure what you were trying to do with your previous code, except you tried to write to the table 3 times
    instead I'd suggest you write the values after getting all 3 new settings

    just as a personal comment
    your varaible naems are not especially helpful, byut that its not immediately clear what they are

    eg instead of
    Dim Bhar As String
    Dim Twnar As String
    I'd probably use somethign like
    Dim BoroughName As String
    Dim TownName As String
    if I was going to use cryptic names I'd probably want to comment them on declaration
    Dim Bhar As String 'stores the Borough Name input by the user
    Dim Twnar As String 'stores the Town Name input by the user

    there's no comments in the function of code trying to explain what you are upto. granted this isn't the hardest code to understand but its alsways a good idea to docuemnt what you are doing if its not immediately clear what you are doing
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I agree with Healdem when he writes that you should try to give more meaning names to the variables, even if it does not change the way the code works.

    Here's a solution that was tested with your sample database:
    Code:
    Private Sub ComboPostcodes_NotInList(NewData As String, Response As Integer)
    
        Const c_MsgPostCode As String = "Add '@P' as a new Postcode?"
        Const c_MsgTown As String = "Please enter a Town to associate '@T' with"
        Const c_MsgBorough As String = "Please Enter a Borough to associate with '@P' and '@T' "
        Const c_SQL As String = "INSERT INTO PostcodesTBL ( Postcode, Town, Borough ) " & _
                                "VALUES ( '@P', '@T', '@B' );"
        
        Dim strPostCode As String
        Dim strTown As String
        Dim strBorough As String
        Dim booOKToAdd As Boolean
    
        strPostCode = NewData
        If MsgBox(Replace(c_MsgPostCode, "@P", strPostCode), vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then
            strTown = InputBox(Replace(c_MsgTown, "@T", strPostCode), "Enter Town")
            If Len(strTown) > 0 Then
                strBorough = InputBox(Replace(Replace(c_MsgBorough, "@P", strPostCode), "@T", strTown), "Enter Borough")
                If Len(strBorough) > 0 Then booOKToAdd = True
            End If
        End If
        If booOKToAdd = True Then
            CurrentDb.Execute Replace(Replace(Replace(c_SQL, "@P", strPostCode), "@T", strTown), "@B", strBorough), dbFailOnError
            Response = acDataErrAdded
        Else
            ' Eventually provide a message box saying that the postcode was not added.
            Response = acDataErrContinue
        End If
            
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    56

    Thanks guys

    Both Peices of code worked nicely, and siindho, i like the way it doesn't add anything if a town or borough isn't specified, very nice.. although, the code is very advanced im having trouble translating it (understand what its doing)

    i don't know what Const does or how to use it,
    how does it recognise '@p' as postcode?

    man its complicated but it works beautifully!

    thanks alot,

    P.s posting a new issue to quiz your brain :-D

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you may have a problem with your design if say somene enters a shorter postcode after a longer one

    in my tests I tried SK10 then added the town & borough, then tried to add SK1 but the combo wouldn't accept it instead overriding with SK10
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2011
    Posts
    56

    ok..

    right ok,

    thanks for your input,

    which code is that based on, Siindho's? or yours?

    Which code will work more accurately and efficiently?

    thanks

    Retarded Jack,

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Quote Originally Posted by jackjsmith88 View Post
    i don't know what Const does or how to use it,
    how does it recognise '@p' as postcode?
    Const is an instruction used to declare a constant, as Dim is used to declare a variable. The value of a constant cannot be modified during the execution of the code, hence its name. As the value of a constant is known at compile time, the compiler can optimize the code and produce a more efficient program.

    @P, @T and @B are placeholders (remember that the contents of a constant cannot be changed at execution time). They are replaced by the actual values using the Replace function:
    Code:
    Replace(<Original String>, <Search SubString>, <Replace SubString>)
    This function can be used recursively, as in:
    Code:
    CurrentDb.Execute Replace(Replace(Replace(c_SQL, "@P", strPostCode), "@T", strTown), "@B", strBorough)
    Which could be written:
    Code:
    Dim strSQL As String
    strSQL = Replace(c_SQL, "@P", strPostCode)  ' Replace "@P" with the value of strPostCode.
    strSQL = Replace(strSQL, "@T", strTown)     ' Replace "@T" with the value of strTown.
    strSQL = Replace(strSQL, "@B", strBorough)  ' Replace "@B" with the value of strBorough.
    CurrentDb.Execute strSQL, dbFailOnError     ' Execute the resulting SQL statement. 
                                                ' Note: CurrentDb is a shortcut for WorkSpaces(0).Databases(0), i.e. the current database.
    Have a nice day!

  9. #9
    Join Date
    Oct 2011
    Posts
    56

    Sweet!

    Nice one mate, helps me out a bit im sure ill figure it all out in the end,

    what about what "healdem" said? Post 5 i beleive..

    also, if you have time, could you loook at my secondary thread please? calculations/fields? thanks man

    much love

    Retarded jack

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem healdem mentions is not specifically related to the code used to add new Postcodes. With its properties defined as they are, the combo auto-completes what's typed, which indeed prevents from adding a shorter Postcode if a longer one beginning with the same characters already exists.

    A proper solution to this would consist in changing the behaviour of the combo, which would mean changing several of its properties (Bound/Unbound, RowSource, RowSourceType), which would mean modifying the architecture of the whole form.

    Here's a workaround that does not implies such deep changes (but remember it's just a workaround). It consists in typing a "special" character instead of the Enter key (I chosed the "+" key but it can be changed) to signal that what you typed so far actually is a new PostCode. To implement it, simply add the following code into the existing Form module:
    Code:
    Private Sub ComboPostcodes_KeyPress(KeyAscii As Integer)
    
        Const c_KeyPlus As Integer = 43 ' KeyAscii 43 = '+'
        
        If KeyAscii <> c_KeyPlus Then Exit Sub
        If Me.ComboPostcodes.SelStart > 0 Then
            Me.ComboPostcodes.Text = Left(Me.ComboPostcodes.Text, Me.ComboPostcodes.SelStart)
            SendKeys "{ESC}"
        End If
        
    End Sub
    I'll have a look at your second question as soon as possible.
    Have a nice day!

Posting Permissions

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