Results 1 to 6 of 6

Thread: Update fields

  1. #1
    Join Date
    Jun 2017
    Posts
    4

    Unanswered: Update fields

    Quick question and ANY HELP IS APPRECIATED!
    Obviously I have an ACCESS DB - 2007.

    1. I have only one table. Because of user request I have had to create 2 forms. Here is what I am looking for(Whether it code or something simple). When I enter ANY text into the text box on one form I would like another field, which is a combo box on the other form, to return the value of YES.

    below are the names
    Table: Alumni Master
    Form(where text box exists): Alumni Master Form
    Form(where combo box exists): UpdateRecordForm

    As I said Any help would be appreciated!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    602
    Provided Answers: 31
    I don't see why you need 2 forms....
    One form can show all records (continuous) and show the combo box too.
    A text box in the header will allow you to search by entering the vb code into the textbox AFTERUPDATE event:

    Code:
    Sub txtBoxFind_afterupdate()
    If IsNull(txtBoxFind) then
      Me.filterOn = false
    Else
      Me.Filter= "[lastname]='" & txtBoxFind & "'"
      Me.filterOn = true
    End sub

  3. #3
    Join Date
    Jun 2017
    Posts
    4

    Update Fields

    Thank you for your reply. I know and you know one Form would be great....However....the user wants a 2 forms based on the table and I have to make them happy....

    Everything else in these forms work as they should.

    The 2 forms are:
    1. Add new Record
    2. Update Records

    The text box is in Add New records and combo box is in Update Records. I need a way to autopopulate the combo box based on the text box, meaning there are 2 conditions:

    1. If there is no entry in the textbox then the value in the combobox is NO
    2. If there is any entry in the textbox then the value in the combobox is YES.

    Any help would be appreciated!

  4. #4
    Join Date
    Jul 2017
    Posts
    6
    What is the purpose of the combobox? Are "yes" and "no" the only values that will be available in the combobox list? Are the Y/N values used for something later on, or are they just a visual cue for someone?

    Rusty

  5. #5
    Join Date
    Jun 2017
    Posts
    4
    I appreciate the response.
    The combobox will only have fields Yes and No.
    The Yes/No in the combobox are use for reporting later on as well.

    I have tried to keep to one form but the users want the 2 forms and at this point I really don't feel like arguing with them.

    Simply put: I need a piece of code that does this:
    When there is any input in the text box on Form 1 then the combobox on Form 2 returns the YES value. If there is no input on the text box in Form 1 then the Value would be NO in Form 2.


    Again, thanks for your help!

  6. #6
    Join Date
    Jul 2017
    Posts
    6
    Quote Originally Posted by tpanebianco View Post
    I appreciate the response.
    The combobox will only have fields Yes and No.
    The Yes/No in the combobox are use for reporting later on as well.

    I have tried to keep to one form but the users want the 2 forms and at this point I really don't feel like arguing with them.

    Simply put: I need a piece of code that does this:
    When there is any input in the text box on Form 1 then the combobox on Form 2 returns the YES value. If there is no input on the text box in Form 1 then the Value would be NO in Form 2.


    Again, thanks for your help!
    Why do you have to have a combobox? A combobox is not really going to do what you want it to. Why not a textbox or control on Form 2 that simply displays "yes" or "no" depending on the presence or otherwise of some text in the textbox on Form 1? If you are to use this "status" later then you could bind the textbox or control on form 2 to a table and store the result there, ready for later use. I created a database to test what you wanted and got it to work just fine - with a textbox on form two.

    My steps were:

    Using vba, I set the textbox on form 1 to a Null value.

    I then wrote a simple If/Then scenario to test whether the textbox was still null when a record was updated. The If/Then statement was used to change the value of a global variable (you can use anything you want here - "yes" or "No", "True" or "False" - it's not that important.)

    At this point you could do a couple of things: You could have a change of status of the textbox on form 1 (from null to not null) open the other form (Using the "After Update" Event for the the text box) or you could open the other form manually. If you want the second form to be always open and have the "combobox" status update dynamically, then everything that follows is of no use to you - it would involve a lot more coding than I went into for this exercise.

    However, if the second form is to be opened at some point in time after the data on form 1 has changed, then you could do the following: I then used the "On Load" Event (of form two) to run code that checks the global variable I created earlier. Using another simple If/Then test, if the global variable was "yes" or "True", I used vba to set the value of the textbox on form two to "Yes", otherwise the Else part of the If/Then test was used to populate the textbox with "No".

    Without knowing exactly what happens on form two after this textbox is viewed, it is difficult to commend any other action to you except that at some point you would have to set the value in the textbox on form two to a null value so that the user would not be "tricked" by a lingering, earlier result.

    Some things are quite unclear about the way you intend for this database to work. For example, I am unsure whether this "test" for any text in the textbox on form 1 is a test to find out if there is any text in any record on form 1, or whether it only tests the last record saved? If it is the former, then you would be better off using a control to store the text, not a textbox, and you could then use a simply query to see if that control has been updated in any record.

    Also, how you intend to use the yes/no information on form 2 would be useful to know. If "yes" in your "combobox" will trigger the user to take some action, then this could just as easily be achieved with a button on the form which, when clicked, would execute the code or macro or whatever to achieve what the user wants. Or you could use vba to automate the whole thing, right from the time the data was entered on form 1.

    Your limitation of 1 table is problematic too. It would seem that you are creating a flat file, rather than a relational database. If so, things could get really ugly as you data grows. Also, if you really were to use a combobox on form2, then at the very least you would probably have another table, just to store the yes or no values as a record source for the combobox. I have a feeling that there could be a million ways to achieve what you need to so, perhaps a short precis of what the database will do will help us to help you?

    Cheers

    Rusty

Posting Permissions

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