Can't add record

[debbieg]

I have a single form form based on a query with one table. It has a tab control with 5 pages -- no subforms. The form has an unbound combobox to look up records based on the same query.

If the DriverTypeCD is 1 (Non-Professional) a control is made visible that is required for Non-Professional. It is not visible if DriverTypeCD is 2 (Professional).

Everything worked fine until I did the following:

I added an option group (FrameShow, Default Value = 2) with two toggle buttons - one for Professional (value=2) and the other for Non-Professional (value=1).

In my query I added

WHERE (((DRIVERS.DriverTypeCD)=[forms]![frmDrivers]![FrameShow]))

I also added this code so that my combobox matches the records.

Private Sub FrameShow_AfterUpdate()
    If Me.FrameShow = 2 Then
        Me.Requery
        Me.LookUp_combo.Requery
        Me.LookUp_combo.SetFocus
    ElseIf Me.FrameShow = 1 Then
        Me.Requery
        Me.LookUp_combo.Requery
        Me.LookUp_combo.SetFocus
    End If
End Sub

This works great to look at records.

However, when I attempt to add a record it gives me an error if I try to add a Professionial record -- it wants me to choose something from that invisible control. The same thing happens if I change a Non-Professional to a Professional.

I tried setting the recordsource without the WHERE when I click my ADD button but that didn't help.

Do I need to change my option group to use 3 radio buttons with one set to All so that I can add/change records? Or what do I need to change to get this to work?

Also, if I add a Professional record I would like the Professional toggle button to be selected after update and visa versa. I want the same thing to happen should I change the DriverTypeCD on an existing record.

I've messed with this all afternoon and can't seem to come up with a solution.

TIA,
Debbie

 

[GingerR]

Hi.

What is the purpose of the option group? Also, the code in the AfterUpdate event of the Option Group--I don't know why you have an If-then-else statement when you are performing the same tasks regardless of the selection?

you say: "when I attempt to add a record it gives me an error if I try to add a Professionial record -- it wants me to choose something from that invisible control". How/When does it want you to choose that? Do you get an error message when trying to save or something? What is it that wants you to enter data into that invisible control? Seems like it would be some code you've written?

When you are trying to add a Professional Record, have you selected PROFESSIONAL in the option box? When you select something from the Option Box, does it set that one control to be visible or not?

What was the code you tried in your ADD button's OnClick event to change the form's recordsource?

Seems like there is some disconnect between all of the pieces. What are you trying to accomplish with the option box? Is it bound, or unbound?

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[debbieg]

GingerR,

Thank you for responding.

What is the purpose of the option group? What are you trying to accomplish with the option box? Is it bound, or unbound?

To change the parameter in the form and combobox's query. It is unbound. I want the users to be able to be able to view only the Professionals or Non-Professionals and I want the combobox to display only those also.

What is it that wants you to enter data into that invisible control? Seems like it would be some code you've written?

In the Form_Current and DriverTypeCD_combo_AfterUpdate() I have:

    If Me.DriverTypeCD = 1 Then
        Me.DriverUseCD_combo_Label.Visible = True
        Me.DriverUseCD_combo.Visible = True
    ElseIf Me.DriverTypeCD = 2 Then
        [red]If Not IsNull(Me.DriverUseCD_combo) Then
            Me.DriverUseCD_combo = vbNullString
        End If[/red]
        Me.DriverUseCD_combo_Label.Visible = False
        Me.DriverUseCD_combo.Visible = False
    End If

The red line is only in the AfterUpdate. I think that is the line causing me the error. If the record was a Non-Professional with a value in DriverUseCD_combo and I'm changing it to Professional I need the value in DriverUseCD to be deleted.

When you are trying to add a Professional Record, have you selected PROFESSIONAL in the option box?
What was the code you tried in your ADD button's OnClick event to change the form's recordsource?

I tried this code:

    Dim strNewSource As String
    strNewSource = "SELECT DRIVERS.SSN, DRIVERS.DeptCD, DRIVERS.HomeTerminalCD, " _
        & " DRIVERS.[Driver ID], DRIVERS.DriverTypeCD, " _
        & " [Last Name] & "", "" & [First Name] & ("" "" + [MI]+""."") AS Name, " _
        & " DRIVERS.[Last Name], DRIVERS.[First Name], DRIVERS.MI, DRIVERS.Address, " _
        [red]etc., etc., ...[/red]
        & " FROM DRIVERS " _
        & " ORDER BY DRIVERS.[Last Name], DRIVERS.[First Name]"
    Me.RecordSource = strNewSource
    
    Me.AllowAdditions = True
    
    DoCmd.GoToRecord , , acNewRec
    Me.[Last Name].SetFocus

I just copied the SQL from my query and removed the WHERE clause.

I am not using the code in RecordSource in my ADD button and must have done something else and I can now add a record whether Professional or Non-Professional option button is selected. I just need to add some code to select the correct option button and stay on the new record. Hopefully, that shouldn't be a problem.

It seems my only problem is changing a Non-Professional to a Professional.

Thanks,
Debbie

 

[debbieg]

I needed to just play with this a little longer ... I figured it out. VBA makes me crazy sometimes!

This following code works allows me to change a Non-Professional to a Professional:

    If Me.DriverTypeCD = 1 Then
        Me.DriverUseCD_combo_Label.Visible = True
        Me.DriverUseCD_combo.Visible = True
    ElseIf Me.DriverTypeCD = 2 Then
        If Not IsNull(Me.DriverUseCD_combo) Then
            Me.DriverUseCD_combo = [red]Null[/red]
        End If
        Me.DriverUseCD_combo_Label.Visible = False
        Me.DriverUseCD_combo.Visible = False
    End If

I also got it to select the correct option button and stay on the new or edited record.

Sorry for wasting your time.
Debbie