Forcing user to enter data into certain fields

[jlee9562]

Hi CJ,

Yes, the table seems to already be a recordsource to the form but when I open the form a completely new blank form appears with no records to scroll to or from. The only time I can scroll through to another record on the form is when I enter more than one record on the form without closing out the program.

Is the lookup form you're talking about something like this? http://allenbrowne.com/ser-03.html. Or a completely different form?

Db attached for your review.

Thx!
Jim

 

[CJ_London]

Hi Jim,

As far as I can see, you just need to set the forms dataentry property to No.

Re the lookup form, I was suggesting more like a navigation form - a simple continuous form which has chartid, id and perhaps initial and reviewer name and abstractstartdate or dateofabstract

This form can then be filtered to find things quickly and clicking on an option takes your to the relevant record

 

[jlee9562]

Hi CJ,

Thank you, changing the dataentry property to No did the trick.

Re the navigation form, if I understand correctly, I will need to create a new form an add those several fields and somehow relate it to my other form? Could you provide an example please?

Thx,
Jim

 

[CJ_London]

this is a very simple form - clicking on the id will take you to the relevant record.

You can modify the form to include additional fields (modify the recordsource and include the additional fields in controls on the form). By right clicking on a field (or using the facilities in the ribbon or the navigation bar at the bottom) you can filter and sort so you can find the record you require quickly (e.g.by reviewer) - using a combo you can only 'find' on one prespecified column (the first visible column) using the typeahead.

I note that your ID field is actually text, with the requirement that all characters should be 0-9 - why don't you make this a number?

Also, you are doing something with the DOA field in the form to reset it every time the form is opened, but for some reason the control is locked but as it needs to be completed to leave the form, you can't! This may just be something in my copy

 

[jlee9562]

Hi CJ,

I changed the ID to text because some of the numbers start with a zero and Access wont let me do that as a number, but maybe there's a way.

Re the DOA field, yes, sorry, I took out the DOA code for your copy only because my PI didn't want the completed db to be made public. Can I send to you privately?

Thx,
Jim

 

[CJ_London]

No need - I was just commenting that there seemed to be a problem but if that is the reason why then it's not a problem!

 

[jlee9562]

Hi CJ,

Hope you're doing well!

We're finally starting to work with the db and there's going to be several people inputting data on the same copy of the program but on their own time and computers. If we would like to combine the data into one db, is there a way to do this?

Thanks,
Jim

 

[CJ_London]

It depends on your setup. In any event I would split the db for ease of maintenance and reduced risk of corruption.

If the computers are connected via a network, I would split the db into front end and back end. Put the back end on the network somewhere where all users can access. Put the front ends on each users machine and link to the back end.

If the computers are not linked via a network you have a number of possibilities, but it does depend on how the system will be used and none are particularly satisfactory.

You could convert the back end into .mdb. If you do this then you can use what is called replication - you have the master back end and each user has a replica. periodically they can send you a copy of their back end and you can synchronise with the master. To find out more about replication search online for 'access 2003 replication'

Alternatively if the work each user is doing can be considered as completely separate from the other users, I would have a user id field in each table and to 'synchronise', for each table in your master, delete all records with that users id then import and append the latest copy from the user.

One other thing I would recommend is that you change autonumber fields from incremental to random - you need to do this for replication anyway if you go that route but it means that any relationships between records can be maintained - each user will create their own set of random numbers, the chances of a clash with another user is limited and easily checked before importing.

 

[jlee9562]

Hi CJ,

I read that replication is one of the most complex features of Access. Do you think it's doable for a novice like myself? I couldn't really find a step-by-step guide for it.

If the computers are connected via a network, I would split the db into front end and back end. Put the back end on the network somewhere where all users can access. Put the front ends on each users machine and link to the back end.

I don't think I quite understand how to split the db into front end and back end. Can you elaborate please?

I also simply tried copying and pasting the data from one Access table into another but that didn't seem to work. My PI has suggested we could just copy and paste each person's data into Excel, which seemed to work so we might just end up doing that.

Thanks,
Jim

 

[CJ_London]

Any form of synchronisation is complex! Copying and pasting is perfectly doable if the table layouts are the same - just highlight an empty row before pasting. Just be aware that the auto number will not always be copied - a new one will be generated by the receiving db for any duplicates, so if you have two tables related by the autonumber field as a primary key, that relationship will be lost (hence my suggestion of using random rather than incremental autonumbers)

Here are some links on how and why to split your db

http://www.techrepublic.com/blog/10-things/10-plus-reasons-to-split-an-access-database/

http://allenbrowne.com/ser-01.html

basically, you put all your tables in the back end and all your queries, forms, reports, macos and code in the front end. Depending on which version of access you are using, the allenbrowne link tells you how to do it - there is a facility in access to split the db for you.