PDA

View Full Version : Design of set up



elsteshepard
05-01-2009, 12:50 AM
Hi, I've created - or should I say, with the help of everyone on here, copied! - a userform which does what i want it to.
It's a change request form, which anyone can complete.
I intended this form to be submitted to the master log for all change requests. It all works lovely.
However, I have one problem, the Master is also used to manage the requests, and therefore may be in use at the point someone 'submits' a new request.
My question is one of design. I assume I have a few options here;

1) Make it impossible to submit a request if the master is open - not very user friendly? (unless someone suggests otherwise)

2) Make the submissoin go to a holding log file - which in turn gets added to the Master (somehow?)

Wondered if anyone has any other ideas - is there a simpler way?

Thanks again for all your help

Bob Phillips
05-01-2009, 03:24 AM
You could log the change requests, and have the master file polling that change request file and applying any changes.

You would need to make sure that each application that updates the change request log (your change forms and the master) open the file, sdo theire stuff, and then close it - in and out quickly.

A better way is to use a real database and update that.

elsteshepard
05-01-2009, 03:50 AM
hmm, someone here suggested a 'proper' database, but i don't know enough about them

also, not everyone here has MS access

I think the polling method would probably suit me best. I don't think there would be a problem with mulitple people submiting forms at the same time - what would the chances of that?! Probably more chance of winning the lottery

All I need now is some code!

Thanks

mdmackillop
05-01-2009, 03:58 AM
Let each use have their own Input Form
The input is sent to a database file (you don't need Access)
The Master pulls data from the database file for processing/reporting

elsteshepard
05-01-2009, 04:06 AM
Ok, so if I wanted to go the database route
currently
I have users complete the userform, then when they click submit some vba puts the contents of the form into a spreadsheet - are you saying I can put the contents straight into a database instead?

Thanks

Bob Phillips
05-01-2009, 05:30 AM
Yep, exactly what I am saying.

You would alos need to retrieve the info from the database.

elsteshepard
05-01-2009, 05:43 AM
ok, i'm pretty sure I don't know enough about this...

1) How can i make an excel spreadsheet a database?
2) How then can i query it to pull details from it to go into my master?

I realise 2 might be a lot of coding, but if you could point me in the right direction i can do some reading.

Thanks

Bob Phillips
05-01-2009, 07:59 AM
I am not suggesting that you make Excel a database, I am saying use a real database.

I will knock up a simple example and post it over the weekend. Do you have an Access licence?

elsteshepard
05-01-2009, 08:03 AM
I have access

The problem is that not everyone here does, so they wouldn't be able to write to it?

would be very grateful

Thanks

Bob Phillips
05-01-2009, 08:50 AM
Yes, writing to an Access database does not require an Access license, but you will need Access tos etup the database to start (you can do it directly from Excel, but that is an unneeded complication.