protect a form and allow to add a record

[Dani]

Hi,
I'm building an Access application to manage various kind of products located somewhere.
I have a table for locations, and a table for every kind of product (because they have different attributes). Locations have an ID, and every product has that ID based on its location: that is how my tables are related.
I'm new to Access but I've already done some macros with Excel, and I'm using Office 2003.
My application now has a main form, on its top user can view and search location, and in the middle there are some tabs that contain subforms, one for every kind of product.
When user selects a location, he can view all products located there clicking on tabs.
Now my problem is that I want to protect products attributes from accidentally editing, but user must have the chance to add a product in a location: I don't know what's the better way to do this, so I need your suggestions and point of view!
Now I have set Recordset property of each subform as Snapshot, so user can only view data but can't edit them.
I thinked to put a button on every subform to open another product form as Data Entry, but in this case I want to catch the location selected when the button is clicked, and automatically put it in the product Data Entry form.
Do you think this is a good solution?
How can I do this?
Thanks.

 

[ajetrumpet]

just remember that you cannot add a new record to a table that is already open. hence, if any of your subforms are pulling data from tables that are going to be required to be written to when you enter new data in the form for data entry, it won't work because it will tell you that the table is already locked for editing.

the best suggestion I have is to do what you already have in mind. the population of the location would be something like this:

docmd.openform formname

forms!formname!locationfield = 

forms!formWhereTheButtonIs!locationfield

 

[Dani]

The main form will stay opened when the user add a new product, so I think I will encounter the problem you said.
Default Access forms are ready to view records and add a new records, my problem is that editing records is "too much easy", but now I remember that there's an option to disallow editing, and tomorrow I will try it!
Thank you ajetrumpet

 

[ajetrumpet]

disallowing edits will not escape the problem I have references, but goodluck to ya!

 

[HiTechCoach]

just remember that you cannot add a new record to a table that is already open. ...

I hate to have to disagree, but that is not a true statement.

 

[ajetrumpet]

I hate to have to disagree, but that is not a true statement.

spoken like a true hardcore programmer.

 

[smig]

I normaly do it with 2 subforms.
the top one (new) I set as "Data Entry" this make it to allow new records only.
the ottom one (show all existing) I set it's "AllowAdditions Property" to false. you can also make it's "AllowDeletions" and "AllowEdit" to false to prevent any change to existing data.

you need to refresh the bottom one after adding a new record.
use the "AfterInsert" event of the top sub form to refresh the bottom one.

 

[Dani]

disallowing edits will not escape the problem I have references, but goodluck to ya!

I think that in my post #3 I have not explained well what I meant.
I meant to directly use subforms placed on main form: disallowed editing will stop user to change data already inserted, but the user can add a new record.
If he wants to edit data he have to click on a button that allow editing.
The save button saves changes and disallow editing again.
That's how I solved my problem for now.

 

[Dani]

I normaly do it with 2 subforms.

This is a solution I've never thinked about, thanks!

 

[leeza]

hi guys,
I have a main form where in a button when pressed takes me to another form where I have a textbox in which I enter the field which is matched wid a field in a table if true then opens yet another form to update tht record containing tht field else it'll go back to the textbox asking to enter the correct data

My problem is tht how do I save the textbox data to match the field in the table.I wrote the VB code for it but it goes in a never-ending loop I don't know what's wrong here's the code for tht

Option Compare Database
Option Explicit
Dim Pro1 As String
Private Sub Pro_Enter()
Pro1 = Pro.Text
DoCmd.OpenTable "TABLE FOR TESTING", acViewNormal, acEdit
End Sub

Private Sub Open_Form_Click()
On Error GoTo Err_Open_Form_Click
Dim stDocName As String
Dim stLinkCriteria As String

If (Pro1 = [TABLE FOR TESTING]![Pro]) Then

stDocName = "UpdateWorkingForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf (Pro1 <> [TABLE FOR TESTING]![Pro]) Then
MsgBox "The Pro number is NOT FOUND", vbOKOnly, "Error"
Pro.SetFocus
Else

Exit_Open_Form_Click:
Exit Sub
End If
Err_Open_Form_Click:
MsgBox Err.Description
Exit Sub
End Sub
Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click

DoCmd.Close
Exit_Close_Form_Click:
Exit Sub
Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub

Pls help me I;m goin nuts

 

[smig]

is it some kind of puzzle game ?
why don't you directly open the last form based on the record selected in the first one ? what do you need the second form (the text box one) for ?

if you use it because you don't have the records on the main form, and you need to select a record won't it be simpler to make it into a combo selecting the correct record ?

 

[leeza]

I agree wid you but the prob is the main form is on the user end and the users need to keep it as simple as pressing a button tht's why when the user presses a button for update a record a form pops up to ask for a pro no and then it opens the table to match the record to the pro no entered and u update the record in the form view and if the pro no doesn't match then the user is asked to enter the pro no again but tht's where I go into a never ending loop n I need help wid tht
Thanx
Leeza