vampyrus

Okay I have a question for everyone out there who uses excel and access

I have included a copy of a spreadsheet template style that I would like to use as a database for my movies so as to keep track of my collection.

What I want to know is if the drop down menu list that I have used for ratings in excel can be recreated in MS Access 2000 instead as access allows for easier usage of checkboxes than excel does ....

I will be looking at creating search and entry forms either the access or the excel versions of the movie lists at a later date that will allow for easy entry, edit and searching of titles in my movie collection ..... to do this I will need to re-learn my VB programming to create the forms with the relevant commands etc ....

If anyone is bored and would like to create an entry form and a search form please feel free to have a go at it lol ....

Okay I have included a very basic set of forms and a table for use with access in case anyone feels like getting adventurous and having a go at the vb programming for me ... along with basic explanation of what I want to do within the fields etc ....

I do realize that what I may be asking for could be complicated so please feel free to let me know if it is very complicated and what I may be able to do to make it less complicated .... any ideas will be well received and equally as well considered ...

Thanks for any help that anybody feels like offering ...

vampy

Attached Files

movie database template.rar (126.4 KB, 2 views)

Glaswegian

Naturally, I disagree with this.

I have my own DVD/Video (yes, I know...) database - I built it a few years ago in Excel. Bear in mind that an Excel sheet has 65536 rows - even I don't have that many films. Just for fun, I built it using only forms - I don't see any worksheets. Access is an excellent tool for relationship databases, but Excel can do many of the same functions, and more, and is easier to use (IMHO).

Can you programme in Excel VBA?

vampyrus

Well sort of a little bit maybe if I can remember what I'm doing ....

It's been quite a while and I don't know if I can remember enough to make it all happen ....

So how do the records display if you don't see any spreadsheets ?

Can I build it in excel using the forms that I have already designed ?? ... which btw ... I have now revised the way everything will look since I posted last night .. I will adjust everything accordingly tonight and repost ....

Glaswegian

They display via a user form - you don't need to use forms exclusively - I did that just for my own amusement, although I rather like the way it turned out.

You should do whatever you are comfortable with - there are no hard and fast rules.

vampyrus

I don't mind the sound of user forms to display the data but I think I don't know enough about the programming side of it to create them all ...

I still haven't had a chance to make the adjustments to all of the forms and stuff that I have here yet .... will re-post as soon as I have made the changes ...

Glaswegian

If you like, I have an early version of my database you can look at. It's not too big (there's only about 3 or 4 entries) but all the code is in place, so it might give you some ideas. If you want it, I'll try and attach it, but if it's over the limit I'll drop you a PM.

vampyrus

That would be excellent .. it would let me see what it looks like in excel and also give me some ideas as to the coding of the forms ....

I would very much appreciate being able to have a look at one completed..

Glaswegian

Here you go.

Any questions, just post back here.

Attached Files

Iain's DVD and Video Database.zip (360.1 KB, 9 views)

vampyrus

Thanks for that ... I will have a look in more detail later however I did play a little with it .... when I tried to click the "new" button i got a runtime error "variable not defined" I have included a screenshot for you to look at.

My next question for you is: Could the coding in that one be used in Access as well, obviously making any relevant changes for referring to access dbase instead of the excel spreadsheet?? If thats what I wanted to do. There is a couple of features in Access that I wanted to be able to use that I have found a bit hard to define in Excel ... namely the checkbox feature. In Access it will automatically define an entire column of checkboxes for you and they are already centred and everything whereas in excel I found that I had to define each checkbox one by one and that I had to also define it's position in it's cell .... If I have to do that then I can't guarantee that ALL the checkboxes will be aligned and if they aren't then the display will look untidy.

Attached Images

error 1.JPG (129.3 KB, 2 views)

Glaswegian

Lol - I haven't played with that version for quite a while.

When doing checkboxes in Excel, I create one, set it up and then simply copy and paste. Excel will paste the checkbox in exactly the same position in each cell as the first one.

I'm afraid I can't help with Access coding - not something I ever managed to learn. However, this site is very good for all Access issues.

vampyrus

lol .. fair enuff then ... any idea what the error might be that it's gicing me when i click the "new" button ...

Thanks for the tips for the checkboxes and also for the website for access help ...

Still a few things I need to figure out before I make any decisions about it anyway .... The list is going to encompass a family collection involving probably about 3 different branches of the family and that's why the checkboxes are involved .... this way I can just have a column for each branch of the family and just check their names when adding new ones so the movie isn't listed more than once and also so that I don't need to clutter up a single column with several names .... In which case I would need to define an act whereby it transferred the checks to the various columns ... if that makes sense ...

Glaswegian

Hmmm....

I don't get that error - check your references in the VB Editor. Go to Tools > References and ensure the following libraries are checked:

Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10.0 Object Library
Microsoft Forms 2.0 Object Library.

vampyrus

it won't even let me select references to check if they are all there ... tis greyed out.

any other way to check them or do u know of somewhere to get them from ?
can i just add them ?

also I am using office 2000 just in case that makes any difference ...

Glaswegian

Hmm...I never used O2K - I'm just wondering if that version of the sort function was not present in 2K. Try commenting out the offending line or lines and see what happens.

vampyrus

Okay I will hav a look at that

thanks


Just went in and tried to comment out lines and i ended up with the following error now

Attached Images

error 2.JPG (110.2 KB, 1 views)

Glaswegian

It's the 'sort' lines you need to comment out. You've commented the first line of the whole routine, so VBA can't find the start - that's why you get the 'Sub or Function not defined' error.

vampyrus

Okay sweet .... by commenting out all of the lines in the sort area as you suggested I can now add new records to the database ....

tyvm .... now I just need to have a look at how hard it is to code and make changes to the layout etc .... and see if i could add the features that I want in it ....

Glaswegian

Glad it's working now. Making changes to forms in Excel is pretty easy. If you need any further help just post back.

vampyrus

okie dokie will do ... thx again

vampyrus

Okay ... i made a cpl of changes that I wanted for myself to the add new VIDEO form ... and am now getting errors .. I have included screenshots of the errors and also included a copy of the modified file ....


I'm also assuming that in amongst this I will hav to add code that will make the checkboxes I have added transfer their ticks into the relevant boxes in the columns .. I have no idea how to do that so would appreciate some input there too

thx

Attached Files

Mick's DVD and Video Database.rar (738.3 KB, 0 views)