Page 1 of 4 123 ... LastLast
Results 1 to 15 of 57
  1. #1
    Join Date
    Apr 2014
    Posts
    89

    Unanswered: VBA CODE Not fully Working

    I have this VBA Code on a form and it hides all the fileds except the Update Address Drop Down box. Once you click on that box it shows all the business name/residence name that are in the FAULT alarm, ONce selected it is then suppose to show all these fields so when you enter all the info it will populate that in the FAULT table. I get an error for the NTC# for invalid qualifer but had diffrent errors before I took out the space. Database is uploaded

    Private Sub SlctNm_AfterUpdate()
    Mailing_Address.Visible = True
    City.Visible = True
    STATE.Visible = True
    ZIP.Visible = True
    Warning_Letter_Sent.Visible = True
    CITED.Visible = True
    NTC#.Visible = True
    lblMailing_Address.Visible = True
    lblCty.Visible = True
    lblState.Visible = True
    lblZip.Visible = True
    lblwarning_Letter_Sent.Visible = True
    lblCited.Visible = True
    lblNTC#.Visible = True
    UpdateAdd.Visible = True
    End Sub

    Private Sub Form_Load()
    Mailing_Address.Visible = False
    City.Visible = False
    STATE.Visible = False
    ZIP.Visible = False
    Warning_Letter_Sent.Visible = False
    CITED.Visible = False
    NTC#.Visible = False
    lblMailing_Address.Visible = False
    lblCty.Visible = False
    lblState.Visible = False
    lblZip.Visible = False
    lblwarning_Letter_Sent.Visible = False
    lblCited.Visible = False
    lblNTC#.Visible = False
    UpdateAdd.Visible = False
    SlctNm = ""
    End Sub
    ************************************************** *****
    I tired adding all the fields into this part of the code and it completely blew up when I added the Mailing_Address not sure how to add the fields that are not here to here. it might be because the code above is not working right I dont know


    Private Sub UpdateAdd_Click()
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("UPDATE FAULT SET FAULT.CITY = [Forms]![Update_Form]![City], FAULT.MAILING_ADDRESS = [Forms]![Update_Form]![MAILING_ADDRESS], FAULT.STATE = [Forms]![Update_Form]![State], FAULT.ZIP = [Forms]![Update_Form]![Zip] " & _
    "WHERE (((FAULT.CITY) Is Null) AND ((FAULT.STATE) Is Null) AND ((FAULT.ZIP) Is Null) AND ((FAULT.MAILING_ADDRESS) IS NULL) AND ((FAULT.[BUSINESS NAME/ RESIDENCE NAME])=[Forms]![Update_Form]![SlctNm]));")
    DoCmd.SetWarnings True
    Call Form_Load
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    I dont think # is a legal object character. Just say lblNTC.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    These are faulty because special characters are used in the names of the controls:
    Code:
    NTC#.Visible = True
    blNTC#.Visible = True
    Use:
    Code:
    Me![NTC#].Visible = True
    Me![blNTC#].Visible = True
    Or:
    Code:
    Me.Controls("NTC#").Visible = True
    Me.Controls("blNTC#").Visible = True
    Have a nice day!

  4. #4
    Join Date
    Apr 2014
    Posts
    89
    OK made some changes so go that part to work now this is giving me error
    Private Sub UpdateAdd_Click()
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("UPDATE FAULT SET FAULT.CITY = [Forms]![Update_Form]![City], FAULT.MAILING_ADDRESS = [Forms]![Update_Form]![MAILING_ADDRESS], FAULT.STATE = [Forms]![Update_Form]![State], FAULT.ZIP = [Forms]![Update_Form]![Zip], FAULT.Warning_Letter_SENT = [Forms] ! [Warning], " & _
    "WHERE (((FAULT.CITY) Is Null) AND ((FAULT.STATE) Is Null) AND ((FAULT.ZIP) Is Null) AND ((FAULT.MAILING_ADDRESS) IS NULL) AND ((Warning_Letter_Sent) is NULL) and ((FAULT.[BUSINESS NAME/ RESIDENCE NAME])=[Forms]![Update_Form]![SlctNm]));")
    DoCmd.SetWarnings True
    Call Form_Load

    End Sub

    Do these names need to line up to the top or do they need to match the Fault Alarm Table?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I hope there are no spaces in [Forms] ! [Warning] (second line of the query).
    2. Create the SQL expression of the query using a String variable, then use that variable as the argument of the RunSQL method:
    Code:
    Dim strSQL As String
    strSQL = "UPDATE FAULT SET FAULT.CITY = [Forms]![Update_Form]![City], FAULT.MAILING_ADDRESS = [Forms]![Update_Form]![MAILING_ADDRESS], FAULT.STATE = [Forms]![Update_Form]![State], FAULT.ZIP = [Forms]![Update_Form]![Zip], FAULT.Warning_Letter_SENT = [Forms] ! [Warning], " & _
    "WHERE (((FAULT.CITY) Is Null) AND ((FAULT.STATE) Is Null) AND ((FAULT.ZIP) Is Null) AND ((FAULT.MAILING_ADDRESS) IS NULL) AND ((Warning_Letter_Sent) is NULL) and ((FAULT.[BUSINESS NAME/ RESIDENCE NAME])=[Forms]![Update_Form]![SlctNm]));"
    DoCmd.RunSQL strSQL
    To debug the sql expression, print the value of strSQL in the immediate windows (CTRL+G), then paste it to a new query in SQL mode. The query analyzer should point out any problem and deliver a (rather) explicit message about it.

    3. Personaly, I would use a dynamic query without any reference to the form:
    Code:
    Const c_SQL As String = "UPDATE FAULT SET FAULT.CITY = '@1', FAULT.MAILING_ADDRESS = '@2', FAULT.STATE = '@3', " & _
                                             "FAULT.ZIP = '@4' , FAULT.Warning_Letter_SENT = @5, " & _
                                "WHERE (((FAULT.CITY) Is Null) AND ((FAULT.STATE) Is Null) AND ((FAULT.ZIP) Is Null) AND " & _
                                       "((FAULT.MAILING_ADDRESS) IS NULL) AND ((Warning_Letter_Sent) is NULL) AND " & _
                                       "((FAULT.[BUSINESS NAME/ RESIDENCE NAME]) = '@6'));")
    
    Dim strSQL As String
    
    strSQL = Replace(Replace(Replace(Replace(Replace(Replace(c_SQL, "@1", Me![City]), _
                                                                    "@2", Me![MAILING_ADDRESS]), _
                                                                    "@3", Me![State]), _
                                                                    "@4", Me![Zip]), _
                                                                    "@5", Me![Warning]), _
                                                                    "@6", Me![SlctNm])
    CurrentDb.Execute strSQL, dbFailOnError
    Note: Depending on the column definitions, it could possibly be (without single quotes):
    Code:
    FAULT.ZIP = @4
    FAULT.[BUSINESS NAME/ RESIDENCE NAME])=@6
    if [ZIP] and [SlctNm] are numeric or if [SlctNm] is boolean.
    Have a nice day!

  6. #6
    Join Date
    Apr 2014
    Posts
    89
    I think I am in over my head

    1 there was a space so I removed that.. When i removed that it seems to work but I get an error that says UpdateAdd.Visible = False That you cant hide a control that has focus. No idea

    2. and 3 U have to use together?

    An I am not really sure what all this means.. Sorry I guess your #3 would it go in the after update procedure? Trying to learn this but it not going so well I guess.

  7. #7
    Join Date
    Apr 2014
    Posts
    89
    Maybe this is just too much with not enough knowledge its the last piece i need to make this all work.. Was doing well with your help might have hit that wall. LOL

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. The control that has the focus (i.e. the "active" control) cannot be hidden. What I usually do for handling such cases is:

    a) Create a TextBox control (Text_Dummy) with the following properties:
    Top: 0
    Left: 0
    Height: 0
    Width: 0
    Special effect: Flat
    Border style: none
    This makes the control invisible while not altering it's Visible property which is set to True. That way, it can receive the focus (you cannot set the focus to a control with its Visible property set to False).

    b) When I need to hide some controls (i.e. set their Visible property to False), I begin with set the focus to Text_Dummy:
    Code:
    Me.Text_Dummy.SetFocus
    That way, you can hide any other control on the form without having to bother whether the control you want to hide is the active control or not.

    2. No, it's one or the other. #3 is an alternate solution that prevents from having references to a form in a query, something that I dislike for several reasons that would be too long to explain here. Moreover, I find it more readable.
    Have a nice day!

  9. #9
    Join Date
    Apr 2014
    Posts
    89
    Ok were do i put the me.text dummy I put it in with the False code but that does not work

  10. #10
    Join Date
    Apr 2014
    Posts
    89
    I think I need to know were in all the code to put it.. I have been putting it in all diffrent places and i get diffrent errors each time. lol
    Last edited by dlafko; 05-09-14 at 18:27.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dlafko View Post
    Ok were do i put the me.text dummy
    You put it wherever you want on the form. Personally I place it in the left top corner (Top= 0 and Left = 0).
    Quote Originally Posted by dlafko View Post
    I put it in with the False code but that does not work
    Quote Originally Posted by dlafko View Post
    says Method or data member not found
    Can you elaborate? What's the line of code that causes the error?
    Have a nice day!

  12. #12
    Join Date
    Apr 2014
    Posts
    89
    i made the text box and changed the name then were do I put this code
    Me.Text_Dummy.SetFocus in the code i already have so that the UpdateAdd.Visible = False does not yeild the error as it still is..

    I tried putting it all over in the diffrent bits of code.

    So I tucked it in like this
    Private Sub SlctNm_AfterUpdate()
    Me.Text_Dummy.SetFocus
    Mailing_Address.Visible = True
    City.Visible = True
    STATE.Visible = True
    ZIP.Visible = True
    Warning.Visible = True
    CITED.Visible = True
    Me![NTC#].Visible = True
    Me![lblNTC#].Visible = True
    lblMailing_Address.Visible = True
    lblCty.Visible = True
    lblState.Visible = True
    lblZip.Visible = True
    lblWarning.Visible = True
    lblCited.Visible = True
    UpdateAdd.Visible = True
    End Sub

    Private Sub Form_Load()
    Me.Text_Dummy.SetFocus
    Mailing_Address.Visible = False
    City.Visible = False
    STATE.Visible = False
    ZIP.Visible = False
    Warning.Visible = False
    CITED.Visible = False
    Me![NTC#].Visible = False
    Me![lblNTC#].Visible = False
    lblMailing_Address.Visible = False
    lblCty.Visible = False
    lblState.Visible = False
    lblZip.Visible = False
    lblWarning.Visible = False
    lblCited.Visible = False
    UpdateAdd.Visible = False
    SlctNm = ""

    End Sub

    so no erros appear but the info i type in the form is not being saved into the Fault form now.. so figure it is something i did.

  13. #13
    Join Date
    Apr 2014
    Posts
    89
    I can upload the database so you can see what the Update_Form is doing and then what it is suppose to do if that would help?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dlafko View Post
    I can upload the database so you can see what the Update_Form is doing and then what it is suppose to do if that would help?
    Please do that. When examining the code you posted, I did not see any line of code that would be able to save anything anywhere, with or without the use of
    Code:
    Me.Text_Dummy.SetFocus.
    Have a nice day!

  15. #15
    Join Date
    Apr 2014
    Posts
    89

    VBA CODE Not fully Working

    Ok here is the database.
    So you will the On the switchboard the Update Address field.
    What it should do is,
    YOu only see the drop down box. You use that to pick a business name/residence name that you want to up date and this has city state zip. You then click update. Works Fine.

    When I added these fields below

    then the Mailing Address, City, Zip Warning Letter sent Cited
    and NTC# was when it all fell apart.

    You enter in the data and hit the update address button which then updates the above information for all those fields IN every name you selected. in the FAULT Table.

    Example I pick Advanced Auto Parts lets say there are 3 of them I enter this data once and all three get updated in the FAULT Table.

    Since I did not get the code we discussed to work fully I am sending the original database with just the working part so you can see how it works and hopefully be able to tell me how to add these other fields. I would also like this on its own form rather than in the switchboard.
    Attached Files Attached Files
    Last edited by dlafko; 05-10-14 at 09:43.

Tags for this Thread

Posting Permissions

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