Solved

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

Posted on 2016-11-20
4
45 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 18

Expert Comment

by:Roy_Cox
ID: 41894593
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 18

Expert Comment

by:Roy_Cox
ID: 41894606
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 18

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41894696
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
ID: 41895294
Particular issues solved.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

831 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