Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2010
    Posts
    22

    Unanswered: Capture User Input for Repeat Use

    I need to be able to collect input from the user so that repetitious data won’t have to be entered each time. In the example below, [Enter Code] is the exact same parameter for each line. In other words, whatever the user enters is applicable for all lines. I have tried to set a variable equal to [Enter Code] to collect the entry, however that didn’t work. I also tried an input box but that didn’t work either.

    DoCmd****nSQL "UPDATE tbl_Name SET Code = [Enter Code] WHERE Code is NULL;"
    DoCmd****nSQL "UPDATE tbl_Address SET Code = [Enter Code] WHERE CODE is NULL;"
    DoCmd****nSQL "UPDATE tbl_Street SET Code = [Enter Code] WHERE CODE is NULL;"
    DoCmd****nSQL "UPDATE tbl_City SET Code = [Enter Code] WHERE CODE is NULL;"
    DoCmd****nSQL "UPDATE tbl_State SET Code = [Enter Code] WHERE CODE is NULL;"

    Any ideas are appreciated. Thanks!
    Last edited by db_questions; 02-20-11 at 23:34. Reason: Alert - For some reason DoCmd . RunSQL isn't printing properly

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Using a variable with InputBox() has always worked for me. Have you tried stepping through the code with this approach? When you say that it doesn't work, what do you mean?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2010
    Posts
    22
    Hi weejas and thanks for your reply.

    I previously tried the following with the Input Box:

    Dim UInput As String
    UInput= InputBox("Enter Code")

    I then replaced all the [Enter Code] text in each of the 5 lines with UInput but still had to type the text each time.

    When I say it doesn't work, I mean instead of applying whatever text the user typed to each line, the user is expected to enter the same text 5 times (for each line). I want the user to be able to type text once and programatically apply that entry to all lines.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Right, what you need is this:
    Code:
    Dim UInput As String
    
    UInput = InputBox("Enter Code")
    
    If UInput = "" Then
    
    MsgBox "No code entered - quitting", vbExclamation, "No code to update"
    Exit Sub
    
    End If
    
    CurrentDB.Execute "UPDATE tbl_Name SET Code = '" & [Enter Code] & "' WHERE Code is NULL;"
    CurrentDB.Execute "UPDATE tbl_Address SET Code = '" & [Enter Code] & "' WHERE CODE is NULL;"
    CurrentDB.Execute "UPDATE tbl_Street SET Code = '" & [Enter Code] & "' WHERE CODE is NULL;"
    CurrentDB.Execute "UPDATE tbl_City SET Code = '" & [Enter Code] & "' WHERE CODE is NULL;"
    CurrentDB.Execute "UPDATE tbl_State SET Code = '" & [Enter Code] & "' WHERE CODE is NULL;"
    See if that works for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Dec 2010
    Posts
    22
    Thank you, thank you. This does exactly what I was after!!

    I did have to change [Enter Code] to UInput in the execute statements, but no biggie.

    Thanks again for your help!

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Oh, sorry about that. I got carried away with the copying and pasting.

    Glad the rest of it helped, though!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Maybe I misunderstand, but why not simply use the Default Value? In your form, you can use the AfterUpdate event of the control holding your data to set the DefaultValue for the field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

    Code:
    Private Sub YourControlName_AfterUpdate()
      Me.YourControlName.DefaultValue = """" &  Me.YourControlName.Value & """"
    End Sub
    And, of course, Access has a Shortcut key combination for this, too:

    <Ctrl> + '

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Mar 2009
    Posts
    120
    Quote Originally Posted by Missinglinq View Post
    Maybe I misunderstand, but why not simply use the Default Value? In your form, you can use the AfterUpdate event of the control holding your data to set the DefaultValue for the field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

    Code:
    Private Sub YourControlName_AfterUpdate()
      Me.YourControlName.DefaultValue = """" &  Me.YourControlName.Value & """"
    End Sub
    And, of course, Access has a Shortcut key combination for this, too:

    <Ctrl> + '

    Linq ;0)>

    I have a question about this. I did it for a field in my DB. It worked Great!
    I have other fields I would like ot do this to as well, however when I go to the AfterUpdate event, I do Code Builder I get the same Event Procedure that is on the first field. How do I seperate these in there. I don't usually use SQL and don't know much about it.
    Thank you.

  9. #9
    Join Date
    Mar 2009
    Posts
    120
    I figured it out.

    However it doesn't seem to be working. I did about 10 fields in the Event Procedure. I entered the data and went to a new record but they all turned to '0'.

    Did I do something incorrectly or is that to many?

    Brian
    Last edited by CHI Brian; 02-24-11 at 17:16.

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by CHI Brian View Post
    ...Did I do something incorrectly or is that to many?
    Without seeing your code it's impossible to tell!

    Copy and paste what you have and someone will take a look.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Mar 2009
    Posts
    120
    THanks. I know what i Did wrong. I had the last part of the formula wrong.

    I had
    Me.MyControl.DefaultValue = """" & Me.Mycontrol.Value = """" and this shoudl be & """"

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The devil is in the details, as they say!

    Glad you got it working!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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
  •