Solved

How to Auto-fill data in Database; VBA Code Mixed Combining Two User Forms

Posted on 2016-11-20
4
26 Views
Last Modified: 2016-11-22
I had this question after viewing VBA Code Mixed Combining Two User Forms.

Hi Roy,

I posted my queries in two posts, both of which you have kindly responded to:

1. In the query "How to Auto-fill data in Database" you ask how line Set rng = ("Sheet1, Table1") is working. Simple answer is I don't really know! It probably comes from copying and pasting related to the second query; I was not aware that I could use use the Table format to automatically extend the data set! I've since made the data range Table1 with the code =OFFSET(Sheet1!$A$1:$H$11,1,0,COUNTA(Sheet1!$A$2:$H$15000),8) so hope that could take care of the auto updating but haven't been able to test it.
2. "VBA Code Mixed Combining Two User Forms" is where I have cobbled together from different sources (copying and pasting various bits of code), but I have been struggling with this for more than a few days now. I don't think you have assisted me directly however it is highly likely I have picked up something for your efforts elsewhere!

I really just want to get this project out of my hair so bearing my mind I am really a novice, feel free to doctor/streamline my file wherever you think its wrong, however your comments where such changes are made would be appreciated so that I can try to learn from my mistakes.

I've just seen your most recent response and will take a look at this now. If you can consider the above comments when you have more time I would appreciate any updates that may be forthcoming.

Regards,
Paul
0
Comment
Question by:Paul Clayton
  • 3
4 Comments
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
I've corrected most of the problems in the other posts.

Change this line

Set rng = ("Sheet1, Table1")

Open in new window


to

Set rng =sheet1,Range("A1").CurrentRegion

Open in new window


This has been done

You seem to be mixing code from all sorts of sources. I have tidied up a lot of it, when I have time I will go through the complete code and tidy it up, but I think it is working now.


You may have trouble with the DatePickers, not every computer can use the DatePicker control. I usually use a non ActiveX version, samples on my web site

Alternate DatePickers for Excel

Take a look at my DatabaseForm which does everything that you are trying

An Excel DatabaseForm to adapt for your own projects

I'll have a look at streamlining the code, hopefully I will be finished in time to look at it this evening. In the meantime be careful when mixing code from different sources.
00-Test_User_Form-18Nov--1-.xlsm
0
 
LVL 17

Expert Comment

by:Roy_Cox
Comment Utility
There's been a change of plan for today, so I am working through your code and will post a revised workbook later.
0
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
Comment Utility
Ok

I've worked my way through the code and made amendments

I've created a procedure that will load the TextBoxes and is shared by several controls, this eliminates writing the code for each button.

When adding a new line I am assuming that the date will be the next day, so the code autofills the day and date boxes.

I'm not sure what the reports are. Are you looking to print a report between two dates?
00-Test_User_Form-18Nov--2-.xlsm
0
 

Author Comment

by:Paul Clayton
Comment Utility
Particular issues solved.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now