Questions on Access 2000

[smjohns]

Hi,

Can anyone help with the following questions I have on Access 2000. I am still fairly new to all this so hope these questions are not too basic.

1) I am creating a form where certain fields need to be available for entry or protected based on data entered in other fields. I also want to be able to protect all fields, if a status field is set to "CT" and that change has been saved.

2) I have created a form to amend a record (single record at a time) and on this form is a subform which enables the addition of multiple child records on a seperate table. This all works very well but I have come across a problem on the main form. I have added a close form button and added a piece of functionality to check for changes to the main form. If changes exist, it issues a message asking if changes are to be saved before closing. I used the me.dirty command to achieve this. This works ok if the cursor is placed in one of the fields on the main form. If the cursor is placed in a field on the displayed subform, clicking on the close button just saves all changes on both forms and closes the screen. Is there anyway to force the close button to check for changes on a particular form? I have tried forms![formname].dirty but this does not seem to work. If this is not possible, is there a way to force the cursor back to tab position 1, before checking for changes. I have tried requery, but this commits the record changes.

3) I have set up some fields which have a list of values behind them but are also manually enterable. Is there a way of validating the entered data against the LOV as the user leaves the field? Additionally is there a way of preventing the user from manually entering data, forcing them to select from the LOV?

Thanks for any help you can provide.

Kind Regards

Simon Johns

 

[theprez]

3) You want to use a Combo box. A combo box can have a list of values that the user must select. You can also set a combo box to "limit to list". This forces the user to select something from the list before leaving. The list can be populated from another table, query or values. I usually suggest a table to make it more manageable if you need to add to the list.


2) I think once your user leaves the main form to go to the sub form, their changes are being saved. You may want to have the entries on the main form check on entering the subform. As far as the cursor position, you can use the setfocus command for the field - me.fieldname.setfocus (But I think at that point its too late the data is already saved in the main form)

1)You can use the lock field to lock fields from entry - me.fieldname.locked = true. Don't lock the field where the user puts the CT. If you do, they will not be able to open the other fields back up. So on the field that the user enter CT, on the afterupdate event do something like

If me.status = "CT" then
me.field1.locked = true
me.field2.locked = true
else
me.field1.locked = false
me.field2.locked = false
end if

If there are different combinations other than CT, use a Select Case.

 

[smjohns]

Hi,

Thanks for the quick reply. I shall give these a try over the weekend.

Regarding your point 2, the changes on the main pages are not saved when you click on the sub form although the changes I make to the subform are saved as I update rows. If I click on close when the cursor is on the subform, it does save everything (including the changes to the main form), but if return the cursor to the main form (whether I have made changes to the subform or not), the close button recognises that changes have been made to the main form and asks if I want to save them. I will try your set focus thing and see where that gets me....fingers crossed.

Thanks again for all your great help.

 

[smjohns]

Hi ThePrez,

My humble appologies, you were completely right about the main form record being saved when I clicked on the sub form. Unfortunately, I need a message to be issued before this happens. Do you know what event I can use to trigger a "are you sure you want to save" message for the main screen or a "clicking on this subform will save changes to the main record" message. I have tried lost focus / deactivate on the main form, but that does not work. I have also tried getfocus on the sub form, but still does not work....doh.

I need an event which will enable me.dirty to work on the main form, before it is automatically saved when you click on the subform.

Cheers

Simon

 

[theprez]

Use the before update event for the form.

 

[smjohns]

Thanks the prez, took some effort but got it to work at last !!

Cheers

Si