UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Interactive User Form For Input, Office 2013    
 
   
bakersburg9
post Nov 11 2017, 05:02 PM
Post#1



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


I've been using input forms for years - very simple - I'm no expert - but is there a way to have what I would call an "Interactive" user form (lame name on my part, but I'll explain) ? I'm attaching a user form I used when I was unemployed and was logging my calls - so the "form" would pop up, and I would input into the various boxes, and click the button to write to the Excel worksheet - but these were creating new lines, new records - but what if you didn't want to create new rows of data in your worksheet, but instead edit, and not add new lines/rows of data ? Say where you type an ID number from column A, and the values for that record (selected values from just pertinent columns on the same row for whatever corresponding ID you selected ? I hope that makes some sense - I've attached a copy

Any help would be greatly appreciated.....
Attached File(s)
Attached File  InputFormPhoneNumbers_Test2.zip ( 27.05K )Number of downloads: 5
 
Go to the top of the page
 
ADezii
post Nov 12 2017, 03:43 PM
Post#2



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Do you mean to Update Data in an Excel Row where a Phone Number matches a Number entered on your Phone Input Form? If there is no match on Phone Number then Add the Record?
Go to the top of the page
 
bakersburg9
post Nov 12 2017, 05:23 PM
Post#3



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


Thanks for your input - the attached was just a sample form I've used in the past for something else - Here's what I want it to look like/accomplish
EDIT: There's something in my layout that's confusing, sorry - the user inputs in the form, not in the spreadsheet - well, that's what I want, anyway...
Attached File(s)
Attached File  FormExample1.jpg ( 125.87K )Number of downloads: 2
 
Go to the top of the page
 
ADezii
post Nov 12 2017, 07:00 PM
Post#4



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Still a little confusing, can you Upload the actual Spreadsheet/UserForm stripped of any sensitive Data?
Go to the top of the page
 
bakersburg9
post Nov 12 2017, 10:13 PM
Post#5



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


ADezii,
I will –the screen shot represents three stages – the top part is the Excel file sent to the user - the part in the middle, taking up most of the screen, is what I want the form to look like after the user clicks the button to load the form, AND types in a house key – so they would input the house keey, and all those other fields (just some pertinent ones) would show up (if that’s even possible), and the text box to the right of STATUS and RESULTS would be empty – in this example, the user is typing in “Pending and “Job scheduled. Perhaps that is also confusing, because those words sound like column headers.
This may not even be possible, that’s why I’m asking – I’ll post the actually workbook, with the data stripped
thanks for your input !
Steve
Go to the top of the page
 
bakersburg9
post Nov 13 2017, 11:45 AM
Post#6



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


Here is the actual spreadsheet - the user inputs into the last two columns, AT and AU
Attached File(s)
Attached File  Excel_With_Form_TEST.zip ( 64.75K )Number of downloads: 2
 
Go to the top of the page
 
ADezii
post Nov 13 2017, 01:11 PM
Post#7



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Are you operating within Excel and using a UserForm for Input or are you operating within Access using an Access Form for Input? If you are doing this in Access, is the Excel Worksheet Linked or do you intend to use Automation Code to access it's Data, etc. I need some specific answers before I can even begin to attempt a solution.
Go to the top of the page
 
bakersburg9
post Nov 13 2017, 03:54 PM
Post#8



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


QUOTE
operating within Excel and using a UserForm for Input
Excel.
Go to the top of the page
 
ADezii
post Nov 13 2017, 07:24 PM
Post#9



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I put a little something together for you, it should be at least a good starting point.
    1. Download and Extract the Attachment.
    2. Open the Excel (*.xlsm) File.
    3. Click the Open Input Form Command Button.
    4. A UserForm will activate and contain relevant Data Fields.
    5. For the House Key enter 528653, it is the only House Key and obviously will provide an Exact Match.
    6. Enter Data in the other Fields.
    7. Click the Update From Input Form Command Button. If an exact Match is found in Column B, which should happen, selective Fields will be Updated with the New Values for that House Key in the aligned Columns. I'm not sure about the Field alignments, I will leave that up to you. Enter a Non-Match in House Key and you will get an Error Message.
  2. Good Luck with your Project.

Attached File(s)
Attached File  Excel_With_Form_TEST.zip ( 75.67K )Number of downloads: 4
 
Go to the top of the page
 
bakersburg9
post Nov 13 2017, 10:36 PM
Post#10



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


ADezii ,

First of all, a million "thank you"'s," wow! Based on your reply, it seems you get exactly what I'm trying to do - I have a lot of experience with very simple forms - basically ones that just add a new line - but nothing really happens - do I need to set up references or something ? When I run the form, the house key box looks different - like you said, if I type an invalid key, there's a message - but when I click the update fields button nothing happens - I've EXTREMELY grateful for you setting this up for me, and I'm not asking you to do the coding and all the bells and whistles, I'm just wondering what I'm doing wrong, if you can hazard a guess... that being said, THANKS SO MUCH cool.gif I'll play around with it, see if I can get it to work...

thanks.gif

Steve
Go to the top of the page
 
ADezii
post Nov 14 2017, 08:24 AM
Post#11



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. This is only a Demo, so I simply shortened the House Key Value for ease of entry.
  2. On the Input Form enter 928653 for the House Key. Now, enter Values for Office Phone, City, Status, and Results on the Input Form. Click on the Update Button and you should now see these Values in the Spreadsheet for House Key 928653. They will be in Columns R, U, AF, and AG.
  3. If you still having problems, let me know.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 12:06 PM