Updating Combo box defaults with Optionbox and/or Check box for a Table

Robert88

Robert88

Robbie
Local time
Today, 10:21
Joined
Dec 18, 2004
Messages
335
Hi All,

I am wondering if it is possible using VBA to update using either an option group or check boxes as shown in the frmDefaultValue in the attached file to update two tables tblLabelNumber and tblMediaType.

The form frmMedia contains two combo boxes. I am trying to use the form frmDefaultValue with an option group and check boxes as shown as not sure what is the best method here to update the values in the two table tblLabelNumber & tblMediaType at the push of a button Update as in the form?

Look forward to any comments on this.

Robert88
 

Attachments

Last edited:
Here are details of one form in the attached zip above as maybe my explaination is not clear?

frmDefaultCDDVDLabelling

Media Type Option Group values on the form
OptionCD = 1
OptionDVD = 2

Label Number Check box values
Check_1 = 1
Check_2 = 2
Check_3 = 3
Check_4 = 4
Check_5 = 5
Check_6 = 6

Details of tables in the mdb above

tblLabelnumber (6 records with one fldDefaultnumber selected)
fldLabelnumber
fldDefaultnumber

tblMediaType (2 records with one fldDefaultMediatype selected)
fldMediatype
fldDefaultMediatype
fldMediaSize

I suppose if somehow I can select the option and/or check boxes on the form and then update the tables to reflect what I have chosen on the form?

Any help would be appreciated.

Robert88
 
Hi All,

I am currently trying this based on the mdb attached further up but with no luck.

Code: 
Private Sub CmdUpdateDefaultValues_Click()

'I am trying to reset the fldDefaultMediatype of tblMediatype when the Update button on form frmDefaultValue is selected
Dim OptionCD

    If OptionCD = -1 Then 'I was hoping this would recognise if CD was selected on form frmDefaultValue OptionCD
        'Reset the tblMediatype
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = null;"
        'Update the tblMediatype with CD as the default media type
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = -1 " _
            & "WHERE fldMediatype = CD;"
        
    Else    ' For test purpose if I see the below message box I know it has not worked........
        MsgBox "tblMediaType not update"
    End If

End Sub

Can anyone help?

Robert88
 
Last edited:
Hi All,

So I am going solo here, thanks for all the help, LOL!!!! (Only joking):D

OK so I have code now that is a little closer.

Code: 
Private Sub CmdUpdateDefaultValues_Click()

    If Me.optgMediaType = 1 Then 'If this is true CD becomes default in tblMediaType
        'Reset the tblMediatype
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = null;"
        'Update the tblMediatype with CD as the default media type
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = 1 " _
            & "WHERE fldMediatype = CD;"
        
    Else    'If this is true DVD becomes default in tblMediaType
        'Reset the tblMediatype
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = null;"
        'Update the tblMediatype with DVD as the default media type
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = 1 " _
            & "WHERE fldMediatype = DVD;"
        
    End If

End Sub

One problem with this code for the option group optgMediaType, it manages to delete the entries in tblMediaType but then does not update with the new default value selected. Can anyone help me on this one? This of course is for the database attached in the first posting.

In addition, how do I turn off the message box warning?

Look forward to any help here.:p

Now it is time to concentrate on the check boxes....:rolleyes:

Robert88
 
Hi All,

I managed to get my tblMediaType to update, syntax problem show in red below.

Code: 
Private Sub CmdUpdateDefaultValues_Click()

    If Me.optgMediaType = 1 Then 'If this is true CD becomes default in tblMediaType
        'Reset the tblMediatype
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = null;"
        'Update the tblMediatype with CD as the default media type
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = True " _
            & "WHERE fldMediatype = [COLOR="red"]'CD'[/COLOR];"
        
    Else    'If this is true DVD becomes default in tblMediaType
        'Reset the tblMediatype
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = null;"
        'Update the tblMediatype with DVD as the default media type
        DoCmd.RunSQL "UPDATE tblMediaType " _
            & "SET fldDefaultMediatype = True " _
            & "WHERE fldMediatype = [COLOR="Red"]'DVD'[/COLOR];"
        
    End If

End Sub

Now obviously I cannot use If Then and Else if I have more than two options in a group I am going to try using maybe the Case statement to handle a six option group, if anyone has a better suggestion I would appreciate it.

Still haven't got rid of the messages that popup when the routine updates the tblMediaType.

Robert88
 
Hi All,

Now for the problems I am having with the 6 option group for the label number.

I have the code above within the CmdUpdateDefaultValues but currently it is giving me errors.

Code: 
    ' This is to set the default value (Options: CheckLabel1, CheckLabel2, _
    '  CheckLabel3, CheckLabel4, CheckLabel5, CheckLabel6) in the tblLabelNumber.
    Dim LabelNumberOption As Integer
    LabelNumberOption = Me.optgLabelNumber
    
    MsgBox LabelNumberOption
    
    Select Case LabelNumberOption
    
        Case 1 'LabelNumberOption = 1
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = null;"
            'Update the tblMediatype with CD as the default media type
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = True " _
                & "WHERE fldLabelnumber = 1;"
    
        Case 2 'LabelNumberOption = 2
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = null;"
            'Update the tblMediatype with CD as the default media type
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = True " _
                & "WHERE fldLabelnumber = 2;"
    
        Case 3 'LabelNumberOption = 3
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = null;"
            'Update the tblMediatype with CD as the default media type
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = True " _
                & "WHERE fldLabelnumber = 3;"
    
        Case 4 'LabelNumberOption = 4
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = null;"
            'Update the tblMediatype with CD as the default media type
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = True " _
                & "WHERE fldLabelnumber = 4;"
    
        Case 5 'LabelNumberOption = 5
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = null;"
            'Update the tblMediatype with CD as the default media type
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = True " _
                & "WHERE fldLabelnumber = 5;"
    
        Case 6 'LabelNumberOption = 6
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = null;"
            'Update the tblMediatype with CD as the default media type
            DoCmd.RunSQL "UPDATE tblLabelnumber " _
                & "SET fldDefaultLabelnumber = True " _
                & "WHERE fldLabelnumber = 6;"
    
        End Select

Revised mdb attached.

Any help on getting this going would be appreciated.
 

Attachments

Last edited:
Hi All,

I managed to get it working but changed the fldLabelnumber from "Text", to "Number" within tblLabelnumber.

Another issue now is that when I open the form "frmDefaultValue", I have set the default value of each group option to 1 so it opens always with the top of the option group selected.

Is it possible to have the form open with the default values selected from the tables tblMediaType & tblLabelnumber?

If anyone can help I would appreciate it. :p

Robert88
 
Hi all,

Love the help I am getting here, LOL!!!!

Anyway, I have managed to read in the default values from the tblMediaType & tblLabelnumber tables. The code below is for when the form frmDefaultCDDVDLabelling opens.

What I do not know is how to set the default Group Options default on the form to these values, Can anyone help???

Code: 
Private Sub Form_Open(Cancel As Integer)

    Dim varMediaType
    Dim varLabelNumber
    
    varMediaType = DLookup("[fldMediatype]", "[tblMediaType]", "[fldDefaultMediatype]= -1")
    varLabelNumber = DLookup("[fldLabelnumber]", "[tblLabelnumber]", "[fldDefaultLabelnumber] = -1")
    
    MsgBox "Default Media Type from tblMediaType is " & varMediaType 'For Test purpose
    MsgBox "Default Label Number from tblLabelnumber is " & varLabelNumber 'For Test purpose

End Sub

I look forward to any help. :p



Robert88
 
I finally got it

Hi All,

Finally got there;:eek:

Code: 
Private Sub Form_Open(Cancel As Integer)

    Dim varMediaType
    Dim DefaultMediaType
    Dim varLabelNumber
    Dim DefaultLabelNumber
    
    varMediaType = DLookup("[fldMediatype]", "[tblMediaType]", "[fldDefaultMediatype]= -1")
    varLabelNumber = DLookup("[fldLabelnumber]", "[tblLabelnumber]", "[fldDefaultLabelnumber] = -1")
    
    'MsgBox "Default Media Type from tblMediaType is " & varMediaType
    'MsgBox "Default Label Number from tblLabelnumber is " & varLabelNumber
    
    'To place the Default Value from tblMediaType back to the form frmCDDVDLabelling
    If varMediaType = "CD" Then
        DefaultMediaType = 1
    Else
        DefaultMediaType = 2
    End If
    
    Me!optgMediaType.DefaultValue = DefaultMediaType
    
    'To place the Default Value from tblLabelnumber back to the form frmCDDVDLabelling
    Select Case varLabelNumber
        Case 1  'varLabelNumber = 1
            Me!optgLabelNumber.DefaultValue = 1
        Case 2  'varLabelNumber = 2
            Me!optgLabelNumber.DefaultValue = 2
        Case 3  'varLabelNumber = 3
            Me!optgLabelNumber.DefaultValue = 3
        Case 4  'varLabelNumber = 4
            Me!optgLabelNumber.DefaultValue = 4
        Case 5  'varLabelNumber = 5
            Me!optgLabelNumber.DefaultValue = 5
        Case 6  'varLabelNumber = 6
            Me!optgLabelNumber.DefaultValue = 6
        Case Else 'varLabelNumber outside of a number between 1 & 6.
            MsgBox "No number has been placed in varLabelNumber."
    End Select
    
End Sub

A sample of the mdb is also attached, purely for anyones reference later if they want.

I hope it helps someone along the way........ :p

Robert88
 

Attachments

Last edited:
You must log in or register to reply here.

Similar threads

A
Creating a Sort combo box
Replies
3
Views
467
The_Doc_Man
The_Doc_Man
J
Refreshing List Box
Replies
8
Views
443
JamesJoey
J
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
243
Mike Krailo
Mike Krailo
A
Solved Access Checkboxes choosing their own alignment
Replies
16
Views
499
Uncle Gizmo
Uncle Gizmo
G
Issue with Adding & Updating New Records from other Form
Replies
10
Views
383
Gaztry80
G

Users who are viewing this thread

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