VBA help

neilsolaris

Member
Local time
Today, 20:24
Joined
Apr 19, 2020
Messages
114
Hi,

I'm quite new to Microsoft Access, and have a couple of tasks I'm trying to complete, which I'm hoping someone can give me some advice, so I can get started please?

Firstly, I have been using Excel as a database. I created a form to create a new entry to this database, and when I submit the form, I had written VBA code to automatically auto generate a unique alphanumeric code for the new entry. I've now imported the database to Access, and have created a form. How is it possible to make a VBA code kick in after I submit a new entry? I know I need to also redesign the VBA code to work for Access. I hope I've explained this ok!

The second thing I'd like to do it this. I have an excel worksheet that pulls information from the Excel database I mentioned above, using Vloopup. I'd like it to pull information from the new Access database instead now. What would be the easiest way for me to achieve this?

Many thanks for your help.
 
I would have thought you could use the same code?
I would however try and use the Autonumber field, but that really depends on how many links you have already using your key.
You could test for Me.NewRecord in the BeforeUpdate event of the form and set it there.?

Re your second question, just link to the Excel workbbok from Access and use the data like that.?

HTH
 
Thanks for your advice. You make it sound simpler than I was anticipating.

My code refers to columns and rows, whereas in Excess I read that they are called records and fields? Can the autonumber field create a new unique code, consisting of three letters (first three layers of surname) followed by three numbers (starting from 001 and incrementing until the code is unique)? If so that makes my job much easier.

How do I link excel to Access? I want excel to read the data from Access, not the other way round.
 
Can the autonumber field create a new unique code, consisting of three letters (first three layers of surname) followed by three numbers (starting from 001 and incrementing until the code is unique)?
No, and even if it could, you should not. You can roll your own however. Read up on what auto numbers are/are not.
When you say you 'imported' your Excel thing into Access, that raises a red flag to me. Makes me think your tables schema will be just like your spreadsheets. If that is the case, better stop now and read up on normalization or you will be asking for help here a lot more than you planned.
 
When you say you 'imported' your Excel thing into Access, that raises a red flag to me. Makes me think your tables schema will be just like your spreadsheets. If that is the case, better stop now and read up on normalization or you will be asking for help here a lot more than you planned.

the way you said that made me laugh, Micron! Sad but true in many cases
 
How do I link excel to Access? I want excel to read the data from Access, not the other way round.
hi Neil,

here is how to do it with Excel 365:

Data ribbon tab: Get Data > From Database > From Microsoft Access Database

GetData_FromDatabase_FromMicrosoftAccessDatabase.png


In Excel 2007, it is easier to find ... still on the Data ribbon tab

Data_FromAccess.png


However, do be sure to heed what Micron said about normalizing your data! Otherwise, you will lose a lot of power that Access can give you and get a lot of frustration instead.
 
Thanks for your advice. You make it sound simpler than I was anticipating.

My code refers to columns and rows, whereas in Excess I read that they are called records and fields? Can the autonumber field create a new unique code, consisting of three letters (first three layers of surname) followed by three numbers (starting from 001 and incrementing until the code is unique)? If so that makes my job much easier.

How do I link excel to Access? I want excel to read the data from Access, not the other way round.
Ok, sorry, I mis quoted there.

I think you should be able to use the same 'logic' of the code. Yes, you would refer to fields in a record to get the last used key and then increment it as you wish instead of finding a cell in a row, but the logic would be the same.

However as mentioned you might want to review your structure first as a DB is different to a spreadsheet, although you could have used DB type processes in the spreadsheet like VLOOKUP() and code.

Time will tell. :)
 
Many thanks for your replies. I only just noticed them now!

I'll look into normalization tomorrow, to see what that's all about! Thanks for the heads up.
 
Many thanks for your replies. I only just noticed them now!

I'll look into normalization tomorrow, to see what that's all about! Thanks for the heads up.

you're welcome, Neil ~ here is a free book that will be helpful to read:

Access Basics
http://www.accessmvp.com/strive4peace
Free 100-page book that covers essentials in Access

The book has lots of screen shots, some of which are outdated, but the basics don't change ...

Pay careful attention to the chapters on Normalization and Relationships. There are lots of images, so print it out, get cozy in your favorite chair, lay in bed, wherever it is comfortable for you to learn -- and enjoy ~

Re-read this book until you understand everything there. The more you submerse yourself, the faster you will learn.
 
That's great, thanks so much. I'll definitely read that book, it sounds perfect. That'll keep me occupied during the lockdown!
 
Hi everyone,

I've read through the Access Basic book that Crystal recommended, thanks again for that.

I've just got a few questions to get me to the next stage. To put it into context, I do the bookkeeping for a freelance orchestra, and there are about 700 musicians on the database. On the table I set up, it consists of a unique alphanumeric username for each player (I think I'll keep to this system for now). It also includes their first name, last name, business/bank name (if different from actual name), instrument, home address, mobile phone number, vat number if applicable, bank details etc. Would I be correct in saying all this information can stay on one table? Is there any value to separating it to different tables?

When it comes to paying the players, up until now I enter the players codes into Excel, and the above player data above is automatically entered further along the row, using Vlookup. I enter the fees, descriptions and date of engagement etc in the relevant columns, then Excel calculates the net total fee, VAT if there is a VAT number, and then gross total fee. The next three steps I've automated with VBA, but they include inputting the relevant payment info via into a CSV file, for me to a third party to make the BACS payments, creating and saving PDF files of remittance advice and self billing invoices (depending whether they are VAT registered or not), and emailing these files to the players.

My aim eventually is to transfer this whole process to Access. With that in mind, what is the most efficient way to achieve this? For each new engagement, should I enter the new engagement into a new table each time? Do I need to create a new relationship from the main database to the new engagement each time? What I want to achieve eventually, is if I open up details for a particular player (via a form maybe?), can I create an engagements link showing all the engagement that that particular player has been involved with. Is this all easily possible, and am I on the right track so far?

Many thanks, and sorry for the long text.
 
Hi everyone,

I've read through the Access Basic book that Crystal recommended, thanks again for that.

I've just got a few questions to get me to the next stage. To put it into context, I do the bookkeeping for a freelance orchestra, and there are about 700 musicians on the database. On the table I set up, it consists of a unique alphanumeric username for each player (I think I'll keep to this system for now).
Bearing in mind my signature.

I would keep that as a seperate field as you are used to locating data/data entry with that format, but I would still use the autonumber facility as the real link between tables.
It also includes their first name, last name, business/bank name (if different from actual name), instrument, home address, mobile phone number, vat number if applicable, bank details etc. Would I be correct in saying all this information can stay on one table? Is there any value to separating it to different tables?

I would say so, except for the instruments, as a person could play more than one?
When it comes to paying the players, up until now I enter the players codes into Excel, and the above player data above is automatically entered further along the row, using Vlookup. I enter the fees, descriptions and date of engagement etc in the relevant columns, then Excel calculates the net total fee, VAT if there is a VAT number, and then gross total fee.
That is the beauty of the DB, whilst you can show the name etc for the person, you do not store it, just the PersonID, MusicianID whatever you want to call it.
The next three steps I've automated with VBA, but they include inputting the relevant payment info via into a CSV file, for me to a third party to make the BACS payments, creating and saving PDF files of remittance advice and self billing invoices (depending whether they are VAT registered or not), and emailing these files to the players.

Apart from referring to record and fields instead of rows and columns, the logic should be the same.?

I was initially keeping track of deposits/payments in an Excel workbook, and sending emails from the data in the cells.

I decided to move that to Access and managed it with only a few changes. Now the DB has grown to handle extra tasks, which would have been harder I think in Excel. So be prepared to expand. :D
My aim eventually is to transfer this whole process to Access. With that in mind, what is the most efficient way to achieve this? For each new engagement, should I enter the new engagement into a new table each time? Do I need to create a new relationship from the main database to the new engagement each time? What I want to achieve eventually, is if I open up details for a particular player (via a form maybe?), can I create an engagements link showing all the engagement that that particular player has been involved with. Is this all easily possible, and am I on the right track so far?

Many thanks, and sorry for the long text.

Again, bearing in mind my signature, I will leave more qualified members to advise, but I would have thought you would have an Engagement table along with your Musician table and then have an EngagementMusician table that would link the two.?

Don't rush this. You have a working system that you can still use, and planning the structure correctly will save you a lot of time later and make life easier.
Eg you will likely have a Venue table that would have a foreign key in the Engagement table?

Good luck with it. (y)

HTH
 
Hi Gasman,

Thanks so much for your detailed reply, that's very helpful. Good idea about the instrument field, there are a handful who play both. At the moment I just entered both in the same cell.

I'll have a think about your suggestion regarding the Engagement/Musician intermediate table. I need to get my head around this!

Thanks again.
 
OK, I will explain where I was getting that idea from.

I have a DB that emulates the Gazette that we used to get twice a year when I was in the Mechant Navy with a company called Bibby Line.
That detailed some current info about the company but was more well received due to it also telling you who was where on a certain date.?
So you could see where a previous shipmate was at that time.?

I recreated all of this by having one table that I consider the 'main' table.
I called it Links due to the fact that all it really contains is links to other tables, and only has 5 fields.
ID (Do not just name any Autonumber field just as ID as I did there, but this was my first DB :) )
Date
Ship
Rank
CrewName

From that I was able to reproduce the Gazette at http://www.bibby-gazette.co.uk/ for Ships, & Dates. I was also able to create my own reports for Leave and Crew. Literally all from that one table and the supporting tables of Dates,Ships,Ranks & Crew.

My initial goal was to have the Gazette just as it looked on paper, and I succeeded at that with Ships and Dates.

So getting the structure correct first is important. Too often, even I recognise a badly structured DB, generally by people coming from Excel and thinking Excel. A DB is a different kettle of fish altogether.

HTH
 
Last edited:
I've read through the Access Basic book that Crystal recommended, thanks again for that. ... Many thanks, and sorry for the long text.

you're welcome, Neil, happy it helped you. Your post wasn't so long ...

I would assume that musicians may play more than one instrument, and you might want to prioritize their parts too

> "For each new engagement, should I enter the new engagement into a new table each time?"

no. I see many tables. Better to have more tables and less fields in each. At first glance, here is some of what I see:

Events (what Gasman called Engagements -- but Events is shorter)
EventID, AutoNumber, PK
VenueID, Long Integer, Default Value=Null, FK to Venues
EventName, Short Text, Size=50? (maybe longer?), name of event
DateEv, date/time, Date of event with possibly starting time too
Dur, Long Integer, Duration in Minutes

EventPlayers
EvPlayID, AutoNumber, PK
EventID, Long Integer, Default Value=Null, FK to Events
PlayerID, Long Integer, Default Value=Null, FK to Players
InstrID, Long Integer, Default Value=Null, FK to Instruments
PartNbr, Integer, DefaultValue=Null, 1=First chair, 2=Second chair, etc

to make it quicker to set up records for EventPlayers, you might want to have a tables for Sets and SetPlayers so you can define, for instance, string quartet set1, string quartet set2, full orchestra set, and so on, with the players and roles for each

actually though, to make this even more generic, you might have a table called:
EventRoles
with a RoleID foreign key to a Roles table
that would cover the conductor and other supporting roles as well

Venues
VenueID, AutoNumber, PK
VenueName, ShortText

Players
PlayerID, AutoNumber, PK
InstrID, Long Integer, Default Value=Null, FK to Instruments -- main instrument
PlayerCode, Short Text, Size=20? -- this will have a Unique Index
FirstNameP, short Text, Size=50, first name of player (which might include a middle name too, if they go by it)
LastNameP, short Text, Size=50, last name of player

PlayerInstruments
PlayInstrID, autonumber, PK
PlayerID, Long Integer, Default Value=Null, FK to Players
InstrID, Long Integer, Default Value=Null, FK to Instruments
PartNbr, Integer, DefaultValue=Null, Usual Part Number, 1=First chair, 2=Second chair, etc
Ordr, integer, (order is a reserved word) - order this instrument is played. Maybe main=0

Sectns (both Section and Sections are reserved words)
SectID, autoNumber, PK
SectName, Short Text, Size=20, Section Name (string, wind, percussion) - Unique Index
SectID_, Long Integer, Default Value=Null, FK to Sectns.SectID, for rolling up

This table is hierarchical. Example records
1, String
2, Wind
3, Persussion
4, Woodwind, 2
5, Brass, 2

Instruments
InstrID, AutoNumber, PK
SectID, Long Integer, Default Value=Null, FK to Sectns
InstrName, Short Text

Banks
BankID, AutoNumber, PK
BankName, Short Text, Size=50
ABAnum, Short Text, Size=15 (in US, length=9), ABA routing transit number
CityB, Short Text, Size=30, Bank City
StB, Short Text, Size=2, Bank State abbreviation
ZipB, Short Text, Size=5, Bank Zip Code (maybe longer if you have international addresses)
Zip2B, Short Text, Size=4, Bank Zip Code extension (maybe longer if you have international addresses)

BankBranches
BBranchID, AutoNumber, PK
BankID, Long Integer, Default Value=Null, FK to Banks
CityBB, Short Text, Size=30, Bank Branch City
StBB, Short Text, Size=2, Bank Branch State abbreviation
ZipBB, Short Text, Size=5, Bank Branch Zip Code (maybe longer if you have international addresses)
Zip2BB, Short Text, Size=4, Bank Branch Zip Code extension (maybe longer if you have international addresses)

States
St, Short Text, Size=2, PK, State abbreviation (State is a reserved word)
StName, Short Text, Size=30, State Name

Accounts
AccountID, AutoNumber, PK
PlayerID, Long Integer, Default Value=Null, FK to Players
BankID, Long Integer, Default Value=Null, FK to Banks
BBranchID, Long Integer, Default Value=Null, FK to BankBranches - may not be filled
AcctTypeID, Long Integer, Default Value=Null, FK to AcctTypes
AcctNum, Short Text, Size=20? (usually not >12), Account Number
AcctName, Short Text, Size=50, Name on Account

AcctTypes
AcctTypeID, AutoNumber, PK
AcctType, Short Text,

PK = Primary Key
FK = foreign Key

Be sure not to use any reserved words for names. Always start names with a letter, not a number or special character. Don't use any special characters in names except underscore (_) and don't use spaces!

Problem names and reserved words in Access, by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html

I could go on and on -- the more I read your message, the more tables I see. If I looked at your data, I would see even more! Design will take up to 30% of the time for your project.
Data structure (tables, fields, relationships) is the most important thing to get right. If you do this well, the rest is downhill :)

Coming from Excel, you are really going to need to change your mindset, and perspective, to design a good structure.

As for people -- names and contact information ... I typically store all contacts (humans, companies, organizations, etc) in one place, not have names scattered all over. One person may play several roles -- they may be a musician as well as a conductor upon ocassion, and fill in with other roles. So the contacts table is just generic information about people that is linked to tables with related information such as phone numbers, email addresses, and addresses. CID is the primary key in the Contacts table.

In a Players table, I would also store CID as a FK -- so that eliminates the need to put any fields there that the generic contact structures already store.

Here is a "simple" contact management database application in Access that you can download and play around with. Everything is open so you can also look at its design, and see how things were done. There's lots of sample data in there. I also made an easy way to delete the sample data, although the web page may not mention that ~ there's a lot it doesn't mention! Best is to just go download it and try it for yourself. Click on everything and see what it does. Since the data in there is made up, you can't hurt anything ;)

MyContacts
https://www.msaccessgurus.com/tool/MyContacts.htm
 
Last edited:
Many thanks Crystal for your detailed reply, I really appreciate your help. I need to read through it again, but I think I understand most of it. Also, thanks for the practice file to play around with. I'll give that a go.

I'll probably be back soon with some more questions!
 
I've got a hopefully simple question! I want to implement most of the ideas that you suggested Crystal. That would involve me effectively cutting and pasting field columns from the main table to new tables. I thought that would be straightforward, but I can't work it out! What's the easiest way to go about it please? By the way, I'm using Access 2007.

Also, I'm in the UK, and I think we have a slightly different banking system here. I think the unique player code I have is also the BACS reference number, which I probably need to keep. Therefore, is it advisable to do as Gasman suggested, and use the autonumbering system in addition to my unique alphanumerical system on the main table?

Many thanks.
 
hi Neil,

I've got a hopefully simple question! I want to implement most of the ideas that you suggested Crystal. ... Many thanks.

you're welcome!

That would involve me effectively cutting and pasting field columns from the main table to new tables. I thought that would be straightforward, but I can't work it out! What's the easiest way to go about it please? By the way, I'm using Access 2007.

I'm not sure what you mean. First create the data structure and relationships between tables.

Enforce Referential Integrity (RI) on Access Relationships

Then you can append data from other places into the new tables using Append and Update Queries.

This video shows an update query:

Import Excel Spreadsheet into Access

This video shows an append query:

Shuffle Data Imported from Excel

Also, I'm in the UK, and I think we have a slightly different banking system here. I think the unique player code I have is also the BACS reference number, which I probably need to keep. Therefore, is it advisable to do as Gasman suggested, and use the autonumbering system in addition to my unique alphanumerical system on the main table?

I would still suggest putting an AutoNumber into the table for behind-the scenes linking -- performance will be better than linking on text. You can set a UNIQUE INDEX on the BACS reference number (Indexed = Yes (No duplicates) ... in the lower pane when a field is selected in the table design)
 

Users who are viewing this thread

Back
Top Bottom