Forms w/ MS Access 1

[jbh1378]

Any help with the following would be great. Thanks in Advance.

I have set up a new db with about 6 tables. The main table is Lease Details and the rest are child tables (many relationship, Options, Obligations, Rights, Notes, etc...

I need to set up a Data Entry Form that hold all the information entered when a new lease is abstracted. I have set up a form with subforms driving everything off Lease Number (primary key - autogenerated). Not all the subforms fit on on the main form so i had to set up a second form that is accessed by a button.

How can force the Lease Number on the first form to be used as the lease number for the subforms on the second form also.

I also only want the autogenerated lease number to be decided and the information to get entered into the tables when the save button is pressed.

 

[rry2k]

Use a module which is global and define a variable then pass the lease # from one form to another.

 

[jbh1378]

Any Help with the code?

Form1 and Form2 - essentially the same main form just in two forms becasue of size limitations

Both have subforms. Need the leasenumber(primary key in main table) to drive both forms and be associated with the subforms (foreign key in those tables. This number has to be sent to second form when button is pressed to go to second form.

Additionally, How can I have the data enter the table ONLY when user presses Save Button.

Thank You for any Help/Code!!!

 

[rry2k]

I'm not sure if this is what your looking for but if you open a new module and use something like this:
Public vartest As Double
Then you can do vartest = leaseNumber and reference the variable in any form. On the second question, It depends what type of form your using. Was it created with a wizard?
If so you might be entering data directly into the table via your form.

 

[jbh1378]

The form was originally created with wizard. Can I override that so it is not directly entered into the table. If not what are my options

Thanks-

 

[rry2k]

I don't believe you change it. You'll need to create a new form in design mode and then copy the code/properties you need from the old form to the new. Then on the save button call a macro that saves your data to the table. If you take this route and need help with the macro let me know.

Russ

 

[jbh1378]

So I have set up a module which is global to hold the lease number and then created an event to display the lease number in a text box "on load". The first form works as it needs to. The main form's control source is the main table and each subform's control source is their associated table. The subform's are all linked to the main form by Master/Child fields "LeaseNo" When any data is entered into the subform's on the form the lease number in the underlying table is the lease number on the main part of the form(Obviously this is correct).

The second form is opened with a button and also has a control source of the same main table and the subform's are linked also with "LeaseNo". when data is entered into any of the subforms on this second form, the underlying table gets the data but nothing is entered into that table's LeaseNo field. When I make the text box that receives the LeaseNo value from the first form through the global variable bound also to LeaseNo from the main table, it causes a primary key error becasue it says that value has already been entered into the table.

How can I get the subform's on this form to use the lease number in their tables?

Thanks!!!!!
jh

 

[rry2k]

It's hard to answer at this point without actually seeing the app but you might want to try putting a break right before you get the error like on a form open event and check the table at that point and see if there is actually data in the primary key field.

Russ

 

[sspainhour]

The way I pass a number from a main form to a subform is, in the leasenumber field on the subform, I set the default value to the main forms leasenumber.
ex:
In the default value property on the subform's leasenumber textbox put forms![mainform].[leasenumber]

It is a bit simplictic and crude, but it works.

To have the data update only afer a save set the form's BeforeUpdate property to the following event procedure:


Private Sub Form_BeforeUpdate(Cancel As Integer)

' This procedure checks to see if the data on the form has
' changed. If the data has changed, the procedure prompts the
' user to continue with the save operation or to cancel it. Then
' the action that triggered the BeforeUpdate event is completed.

Dim ctl As Control

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record&quot = vbNo Then
Me.Undo
End If
End If

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

 

[jbh1378]

THANK YOU!!!

 

[jbh1378]

I am using this code given to me to update the tables only after the user is prompted to save: This works great BUT there are 3 or 4 subforms on this main form. Anytime a user clicks in a field in a subform, the user is prompted to save. How can get around this. I need it to act like the subform is part of the mainform.

Dim ctl As Control

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record&quot = vbNo Then
Me.Undo
End If
End If

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate

 

[Guest_imported]

How can I make combine the fields, firstname and lastname to automatically fill in a field called fullname?

 

[nogarede]

When I have to many fields and subform on a form I use the "tab control" tools when I create the form. It's simple, clear and standard. It allows you to have many pages, sort the fields by subject....very conveniant.

 

[sspainhour]

In order for the form to only prompt for a save when exiting the mainform, I put a save button on the subforms so after users update data they press the save button in that section.

 

[sspainhour]

Midgetthunder - to combine the fields set the default property to forms![formname].[firstname] & " " & forms![formname].[lastname]
substitutting formname and lastname for the names for your form and field.

 

[jbh1378]

Thanks sspainhour

I tried that originally but whatever has been entered by the user is already in the table whether or not they hit the save button.

The before update worked but it prompts the user to save when they click on a field of one of the subforms.

 

[jbh1378]

rry2k -
can you help me with that macro for the save button that will copy data from one form to a second where it then gets added to the table?

Thank You
jh