Question Getting started with a simple database (2007)

[Bleem]

Hi all,

First post so please be gentle

I'm looking for a bit of advice and direction creating my first functional access database. I'm an IT professional so know my way around access pretty well in terms of being an end-user - but I'm a bit stumped when it comes to building forms and such like.

I have a record kept in Excel that contains a list of book references - I want to move it away from Excel to add some functionality and use Access to keep it up to date with an easy to use GUI.

I'm not too shabby with the actual layout of forms and such like (I use Outlook Forms a lot) so I'm ok with inserting fields, logos and such like.

The columns in the Excel spreadsheet are

Year, Author, Title, Publication, Published By, Pages

From working with Access so far, I think I need the following forms

Welcome Screen (with buttons for the below)
Create New Record
Add Keywords to an existing record
Browse All
Search Records (by Author, Title, Publication or Keywords)

I've played around with the built in Wizards but none of them seem to get anywhere close to where I want to be. I've tried keeping all the data in one table and also splitting it.

I'm looking for someone to tell me if I'm on the right sort of lines here or if I'm barking up the wrong tree all together - baby steps

Thanks all!

 

[statsman]

To begin:

Using the "External Data" function in Access, you can import your Excel spreadsheet into an Access table.

Your welcome form would best be described as a menu.

Most of the actual work in Access is done in Queries. I will generally use 3 forms for each database function.

Form 1 is set to Data Entry. When this form is opened it opens on a new blank record to enter new data.

Form 2 is run from a query and allows you to search for the record you wish. This is used to update or delete a record.

Form 3 is run the same as Form2 except it is set to read only. This is used to view records only. It also restricts the ability of editing or deleting by people you don't wish to give that authority to.

Good luck in your Access adventures.

 

[Bleem]

Thanks Statsman - I've imported all my data and looking good so far.

I've been following the tutorial here to help create a search function on a form but having problems with it.

I can complete all the steps in the flash video without issue, but when it comes to doing a search it just returns 0 records no matter what search term I try.

 

[Bleem]

Ok, I think I know what the problem is - fixing it is another matter though.

When I click the search button, it's looking for records "starting with" the search term.

If I search for example for "Psychology" it only returns those records that start with the word and not those that contain it anywhere in the cell.

 

[Rabbie]

In that case use a search key like "*Psychology*"

 

[Bleem]

In that case use a search key like "*Psychology*"

The search string is being plucked from a textbox in a form though so I'm not sure how that would be done.

The query criteria I have set is

Like [forms]![searchform].[qtitle1] & "*"

where qtitle1 is the text input in the 'search' field.

 

[Rabbie]

No prob

just use

Like "*" & [forms]![searchform].[qtitle1] & "*"

 

[Bleem]

No prob

just use

Like "*" & [forms]![searchform].[qtitle1] & "*"

Fantastic - thank you very much indeed :smashin:

 

[Bleem]

Database all up and running with search working no problem - my next step is to have the search results output to a form of some sort.

How do I pass query results to a textbox on a form?

I'm assuming if there are multiple results found i'll need to add a 'next record' and 'previous record' buttons. If I do this, will it move to the next record in the search results or just the next record in the main table?

 

[statsman]

When you create your data display form, use the query as the source data instead of the table.

You would then:
open the search form
enter the parameter
click SEARCH which would
open the data form which should have your search parameter data listed.

 

[Bleem]

Thanks - I did try that but it didn't work out.

When I tried to use the query as the form source, it popped up with boxes asking me to enter a value for the search parameters, qtitle1, qauthor1 etc

I also took a look at opening the form after the search button was clicked but had no joy with that either - do I need to change the code applied 'on click' ?

Sorry for all the questions - I'm being pressed to get this finished off and I have XNA 3.0/C# to learn next so don't want to get too into VBA if possible