Help please

[eddcole]

Hi there,

I am pretty new to access so please be gentle

I have created a database with 4 different forms that are essentially the same apart from a couple of fields. These all need to update the same table as they all need to get there record number consecutively.

the problem I have is that no matter what form I open it can show the data from the other forms. what I would like is for the information entered on a particular for can only be opened by that form it was entered on.

i.e. if form 2 entered record 20 then form 1 cannot access record 20.

Also could someone tell me how I can lock a record from editing once it is saved so no one else can change the info unless a button is pushed to unlock it.

I am no good a coding so if this is needed then please post it step by step.

Thank you in advance

Edd

 

[Ranman256]

locking,
have a form set property RECORDSET TYPE = SNAPSHOT. (no edits can be made)
click an Edit button, to open an edit form RECORDSET TYPE =DYNASET for edits.

if you want to edit only the 1 record, open using the id....
Docmd.Openform"frmMyForm",,,"[id]=" & txtID

 

[pbaldy]

The multiple forms sounds suspicious. What differentiates them? Why not a single form where you change the visibility of certain items as appropriate?

As to the editing, how do you know record 20 was entered on form 2? Along the same lines as above, I'd still use a single form, and perhaps which records it could access based on your answers to the above.

 

[eddcole]

thank you for the quick reply,

so how can I get the record to change to snapshot after it is saved without doing it manually for example with a button or by pressing new record.

with regard to the viewing of the records, at present I can see the same record in all 4 forms but I only want the form it was created in to be able to view it.

 

[eddcole]

a single form would be ideal but there is different criteria on how a form needs to be presented to different people so the layout needs to change but all of the forms need to be in the same database and if a form is used to fill in the data it needs to follow on from the auto number in the database sheet.

 

[sneuberg]

In the attached database you will find one way of doing the filtering by form. In the table (table1) there's a field name ThisFormName. This is bound to the hidden textbox in both of the two forms (Form1 and Form2). This hidden textbox gets populate with the form's name in the form's before insert. So each record in the table is stamped with the form name of the form from which it was added.

The record source for each form is a query which filters the records base on the form's name.

Concern this button to unlock records I don't understand what you are trying to do. Where is this button to be located? Why is it needed? Wouldn't this prevent the user from editing the data they entered?

 

[sneuberg]

This hidden textbox gets populate with the form's name in the form's before insert.

A simpler way is just to make the form's name the default value for the ThisFormName textbox. For example for Form1 this would be

[Forms]![Form1].[Name]

 

[eddcole]

Cheers guys, now need to know how to create a search box now that can search across all records in the table and display them. this need to also search all fields.