[Excel & Access] Newbie with grand designs requires help

Have you tried turning it off and on again?
User avatar
Rossell
 
Posts: 7527
Joined: Sun Oct 12, 2003 12:21 pm
Location: Kepler-22b

[Excel & Access] Newbie with grand designs requires help

Postby Rossell » Tue Aug 02, 2011 12:32 pm

OK then chaps. Got a task where I want to convert a an Excel spreadsheet to the web and wondering what the best way to go about it is.

Essentially I need to create a front for it which is a search engine which is where you enter criteria, with the next page bringing up abridged results. Clicking on one of those records will bring up the full results of a specific row/field within the spreadsheet.

Sorry if this sounds confusing but its a brand new area to me and I need some expertise where I can but that's the gist of it. Hows the best way to go about it? What system would you recommend?

Am I barking mad? Answers please, thanks :)
Last edited by Rossell on Mon Oct 24, 2011 10:49 am, edited 1 time in total.
Image

User avatar
Dark Flare
 
Posts: 1301
Joined: Sun May 20, 2007 1:11 pm

Postby Dark Flare » Tue Aug 02, 2011 2:42 pm

Are you allowed to just stick it on Google docs? :P
Previously known as HeliX.
Image

User avatar
Rossell
 
Posts: 7527
Joined: Sun Oct 12, 2003 12:21 pm
Location: Kepler-22b

Postby Rossell » Tue Aug 02, 2011 3:28 pm

Its not really built for that without major editing. Unfortunately it looks like what I need will have to be built with ASP, which I have no idea about. Unless anyone else got ideas?
Image

j1nh
 
Posts: 251
Joined: Tue Aug 14, 2007 4:41 pm

Postby j1nh » Tue Aug 02, 2011 7:06 pm

This looks pretty cool

http://www.zoho.com/creator/campaigns/c ... 4QodSxt_fw

Maybe a bit complex if you've never used something like Access before but the basics seem pretty easy to get to grips with - I made this in about 5 mins...

https://creator.zoho.com/j1nhdb/testy/#View:sixB_View

looks like you're limited to 1000 records and 2 'users' (admins) before you have to pay up.

User avatar
Rusty
 
Posts: 1644
Joined: Sun Oct 26, 2003 1:36 pm
Location: Plymouth,Devon

Postby Rusty » Tue Aug 02, 2011 9:19 pm

Is what you're doing really an excel type problem? Is it more a database thing? If so, then you're on firmer ground as pretty much everything on the net is based around databases.

User avatar
Rossell
 
Posts: 7527
Joined: Sun Oct 12, 2003 12:21 pm
Location: Kepler-22b

Re: [Excel & Access] Newbie with grand designs requires help

Postby Rossell » Mon Oct 24, 2011 10:54 am

OK so the website thing won't happen for a while so I'm developing an Access 2003 database (all I can use).

Basically I have one main table. Supplies information to a main form with two subforms. I want to essentially create a Find and Update form so I can search from thousands of records from a few drop down boxes contained on the Main Form. Since there is a lot of duplication I then want a datasheet subform (a third) at the bottom that when the few select combo boxes are ticked with specific options basically operates a query of sorts and displays the results below in the subform. I then want to be able to select that field in the datasheet and the information will come up in the main form fields, which I can then edit and hit an Update button and away we go.

Hopefully I've explained that correctly. I'm pretty sure this a lot of work to be done but just using the Find Record command (which in Access is an absolute pain to use) is just no use. So does anyone have any thoughts?

Thanks so much for any help I receive.
Image

j1nh
 
Posts: 251
Joined: Tue Aug 14, 2007 4:41 pm

Re: [Excel & Access] Newbie with grand designs requires help

Postby j1nh » Wed Oct 26, 2011 2:23 pm

Not sure if this is exactly what you want...

Create a new form. You do not need to link your table to this parent form.

Add a combo box for each field you want to search on and set its row source.
E.g. if you are searching fld1 in table1
SELECT fld1 FROM table1 GROUP BY fld1

give your control a meaningful name, eg, cbofld1

Create a new query and add all your fields.

In Criteria reference the above object
eg, criteria for fld1 would be something like [Forms]![Form1]![cbofld1]

Add each of your search controls to seperate lines so they form OR statements
or on the same line for AND, it's up to you how you want handle this.

Open your form and select some criteria from your combos.

Run your query. It should show records filtered by the form.

Add a subform control to your form and set its source object to your query.

Open the form in form view and select an item from a combo. Nothing happens right? Press F9.

You should now see data.
Back to design view

For each of your combos create an On Click procedure to refresh the form, eg
Private Sub fld1_Click()
Me.Refresh
End Sub

Test your form again, it should now update properly after each selection.

You can now create a new form, set its recordsource to your query and replace the query in your subform with your new form.


Return to Technology & Hardware

Who is online

Users browsing this forum: No registered users and 1 guest