How to control a subform in the Form

B

b_bds

Registered User.
Local time
Yesterday, 21:54
Joined
Jan 17, 2008
Messages
37
Hi

I'm not sure if I'm doing it right but here you have what I want to do: (To help you visualize my form I've added the image of it)

I want to control the record control through a command in the form. I would have a Previous, next command and a Delete. I also want to put an edit so I can extract a record to the different fields and correct it or modify it. The check command is to enter the information in the table (subform).

Actually it's not a regular form/subform relationship because the information above is to choose what the combo XXXXX (actually more than one) will display and then that information will be added to the table (subform).

My question is that I don't know how to control the subform throw the different commands in the form.

I hope I was as clear as it could be but don't hesitate to ask for clarification.

Thanks for your help
Bruno
 

Attachments

  • form.JPG
    form.JPG
    78.5 KB · Views: 165
Well...it would have been helpful to know the names of the Forms but what the heck, ya can't have everything I guess. :)

There are a number of ways to do this but I think in your case it may be just as easy to do it this way but I would really like to know first, are these actual command buttons or are they Label controls?

Previous Button - In the OnClick event:
Code: 
On Error Resume Next
Forms("MainFormName")("SubFormName").Form.SetFocus
DoCmd.RunCommand acCmdRecordsGoToPrevious
DoEvents
If Err<>0 then Err.Clear

Next Button - In the OnClick event:
Code: 
On Error Resume Next
Forms("MainFormName")("SubFormName").Form.SetFocus
DoCmd.RunCommand acCmdRecordsGoToNext
DoEvents
If Err<>0 then Err.Clear

Delete Button - In the OnClick event:
Code: 
[COLOR="DarkGreen"]'Ask User if He/She really wants to Delete the Record...[/COLOR]
If MsgBox("DELETE RECORD!" & vbCr & vbCr & _
          "Are You Sure you want to Delete this Record?" & vbCr & _
          "There is no Undo for this Delete.", vbExclamation + vbYesNo, _
          "Delete Record") <> vbYes Then Exit Sub  'No..then outta here!
[COLOR="DarkGreen"]'Yes...then let's Delete the Record...
'Trap Errors[/COLOR]
On Error Resume Next
[COLOR="DarkGreen"]'See to it that Access does not display the Delete Record Prompt.[/COLOR]
DoCmd.SetWarnings False
[COLOR="DarkGreen"]'Set Focus to the SubForm[/COLOR]
Forms("MainFormName")("SubFormName").Form.SetFocus
[COLOR="DarkGreen"]'Run the Delete Record Command[/COLOR]
DoCmd.RunCommand acCmdDeleteRecord
[COLOR="DarkGreen"]'Let the Form carry through its events before we move on.[/COLOR]
DoEvents
[COLOR="DarkGreen"]'Set the MS-Access Warning systems ON again[/COLOR]
DoCmd.SetWarnings True
[COLOR="DarkGreen"]'If there was any Errors then Clear it.[/COLOR]
If Err <> 0 Then Err.Clear


Edit Button - In the OnClick event:

Why not just Edit the SubForm? Just turn Locks ON or OFF. Curious....

Remove the Bindings on the Main Form Controls that will be used for editing the Record. There should also be a TextBox on the Main Form that can hold the Record ID Number. You need something Unique to each record to be edited. It can be non-visible if you wish. There are easier ways than what is shown below but I really don't know your situation:
Code: 
Dim Frm As Form
Frm = Forms("MainFormName")("SubFormName").Form
Me.SubFormRecordID = Frm.RecordID
Me.Description = Frm.Description
Me.Dia = Frm.Dia
Me.Qte = Frm.Qte
Me.Un = Frm.Un
Me.PrixUn1 = Frm.PrixUn1
Me.Montant1 = Frm.Montant1
Me.PrixUn2 = Frm.PrixUn2
Me.Montant2 = Frm.Montant2
Me.PrixUn3 = Frm.PrixUn3
Me.Montant3 = Frm.Montant3
Me.HHun = Frm.HHun
Me.HHTotal = Frm.HHTotal

Check Mark (Save) Button - In the OnClick event:
Code: 
Dim StrgSQL As String
StrgSQL = "UPDATE yourTableName SET Description=[B][COLOR="Red"]'[/COLOR][/B]" & Me.Description & _
          "[B][COLOR="Red"]'[/COLOR][/B]",Dia=" & Me.Dia & ",Qte=" & Me.Qte & ",Un=" & Me.Un & _
          ",PrixUn1=" & Me.PrixUn1 & ",Montant1=" & Me.Montant1 & _
          ",PrixUn2=" & Me.PrixUn2 & ",Montant2=" & Me.Montant2 & _
          ",PrixUn3=" & Me.PrixUn3 & ",Montant3=" Me.Montant3 & _
          ",HHun=" & Me.HHun & ",HHTotal=" & Me.HHTotal & _
          " WHERE RecordID=" & Me.SubFormRecordID & ";"

CurrentDB.Execute StrgSQL, dbFailOnError
Forms("MainFormName")("SubFormName").Form.Requery

All the code above is just off the top of my head....none of it is tested.

Precautions should be put into place to ensure that a record can not be saved unless that is exactly what is wanted to be done. For example, a blank Record should never be saved.

.
 
Deleting problems

Thanks for your help

Sorry if I didn't say anything sooner but I was busy on an other thing.

The previous and next button work and I'm trying to do the delete button but nothing happens when I click on the delete button.

I used this to delete but no deletion is done. I tried option1 and 2 but still nothing but no errors

Code: 
'Ask User if He/She really wants to Delete the Record...
If MsgBox("DELETE RECORD!" & vbCr & vbCr & _
          "Are You Sure you want to Delete this Record?" & vbCr & _
          "There is no Undo for this Delete.", vbExclamation + vbYesNo, _
          "Delete Record") <> vbYes Then Exit Sub  'No..then exit
'Yes-> Delete the Record...
'Trap Errors
On Error Resume Next
'See to it that Access does not display the Delete Record Prompt.
DoCmd.SetWarnings False
'Set Focus to the SubForm
Forms("MainFormName")("SubFormName").SetFocus
     ---OPTION1---   
        'DoCmd.RunCommand acCmdDeleteRecord
     ---OPTION2---
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoEvents
DoCmd.SetWarnings True
If Err <> 0 Then Err.Clear

End Sub

Any idea what's wrong?
thanks
 
Hmmm...Well, lets find out what's wrong then. Comment the line:

On Error Resume Next

So it looks like this:

'On Error Resume Next

Then rerun the Form. What is the error you are getting and on what code line when you try to Delete?

.
 
Delete fixed

thank you very much for your help... I fixed it by myself... I think it's because I wasn't really putting the focus on the subform.

I'm currently working on gathering the information from the tables so I can populate the subform. When that is done I'll be able to work on the save and edit commands. Don't hesitate to check once in a while to see if I have other questions...

thank you

Code: 
'Ask User if He/She really wants to Delete the Record...
If MsgBox("DELETE RECORD!" & vbCr & vbCr & _
          "Are You Sure you want to Delete this Record?" & vbCr & _
          "There is no Undo for this Delete.", vbExclamation + vbYesNo, _
          "Delete Record") <> vbYes Then Exit Sub  'No..then exit
'Yes-> Delete the Record...
'Trap Errors
On Error Resume Next
'---------
'Set Focus to the SubForm
Me.sfrEstimationResult.SetFocus
With Me.sfrEstimationResult
'Run the Delete Record Command
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End With
'Let the Form carry through its events before we move on.
DoEvents
'If there was any Errors then Clear it.
If Err <> 0 Then Err.Clear
 
Set the OnLostFocus code

Hi

I have a question that I have been looking around on internet but can't find anyting....

Is it possible to set the OnLostFocus event code as I can do with the rowsource.

Let me explain : I have some vba that will run in the click event of a combobox. in that event I set up the visibility of 4 combo boxes and the rowsource. I was wondering how I could set up a filter so users could only enter numbers. Since in other options a combo box is used I have to use a combobox.

here is the code I would like to add to the OnLostFocus.

Code: 
Private Sub cboOption4_LostFocus()
If Not IsNumeric([cboOption4]) Then
   cboOption4.Value = ""
   
   strMsg = "Please enter a number" & vbCr & vbCr
   MsgBox strMsg, vbOKOnly
   Response = acDataErrContinue
    
    Me.cboOption3.SetFocus
    Me.cboOption4.SetFocus
Else
End If

End Sub

edit : other question :
why is it needed to set focus on combo box 3 before setting focus in combo4? I found it by trial and error but can't understant why since the focus is lost and it is on the next command at the resume of the message box
 
Last edited:
Set up the InputMask property of the Control so that it will only accept numbers.

If you were to code this sort of thing then it should go into the BeforeUpdate event so that you can Cancel the input (entry). This way it wont be saved to Table if the Form is bound.

.
 
how to control check box

Hi

Here is another question. I have two check boxes and I want to be able to control them. As for now, when a Area is not in the list I add it with this code.


Code: 
Private Sub cboArea_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
    If i = vbYes Then
        strSQL = "Insert Into tblArea ([AreaName]) " & _
                 "values ('" & NewData & "');"
                 
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
        
        Msg = "Do you want to make" & NewData & "a Active Area ?" & vbCr
        
        i = MsgBox(Msg, vbQuestion + vbYesNo, "Make Area Active")
        If i = vbYes Then
                         
            
            Response = acDataErrAdded
        Else
        Response = acDataErrContinue
        End If
    
    
    Else
        Response = acDataErrContinue
    End If


End Sub

The problem is that it doesn't take care of the check box Active. I want the check box to be valid as default and when I click on it it modifies the field AreaValide in the table tblArea. And I want the check box to look at the table to see if the Area selected in the combo box is active or not.

Same thing with the Sub section Shut-Down. I want the check box to look at the table tblEstimation to see what is the value of SubSectionSD for the same revision, estimation type, Area, discipline, section. As the check box AreaActive does I want the check box to modify all the elements of the table tblEstimation that have the same revision, estimation type, area, discipline and section.

Does anyone have an idea how I could do this?

Thanks
Bruno
 
I don't really get what you mean. Do you mean that you want to place a check march into the Active Check Box if the User decides to select YES when asked if the New Area is to become Active (as indicated within your displayed code)?

if so then add the highlighted to your code:

Code: 
If i = vbYes Then
        strSQL = "Insert Into tblArea ([AreaName]) " & _
                 "values ('" & NewData & "');"
                 
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
        
        Msg = "Do you want to make" & NewData & "a Active Area ?" & vbCr
        
        i = MsgBox(Msg, vbQuestion + vbYesNo, "Make Area Active")
        If i = vbYes Then
[COLOR="Teal"][B]            Me.AreaActive.Value = True
            strSQL = "UPDATE tbArea SET AreaActive=True WHERE AreaName=[COLOR="Red"]'[/COLOR]" & NewData & "[COLOR="Red"]'[/COLOR];"            
            CurrentDb.Execute strSQL, dbFailOnError[/B][/COLOR]    
        Else
            Response = acDataErrContinue
        End If
 [COLOR="DarkGreen"][B]       ......................
        ......................
        ......................[/B][/COLOR]

Use the same principle for your other Check Box.

.
 
thanks it worked well
 
Last edited:
You must log in or register to reply here.

Similar threads

C
Solved No Current Record on Access Close
Replies
11
Views
581
Pat Hartman
P
M
Solved Controlling ScrollBars on a subform
Replies
12
Views
784
riktek
riktek
E
Solved Refer to Subform control
Replies
9
Views
302
cheekybuddha
cheekybuddha
D
After update
Replies
17
Views
466
Pat Hartman
P
E
Passing form's Data to different forms in a project
Replies
6
Views
458
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom