Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

829 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