Record Not Saved In Table & Does not show up in the Form

[andrewdb]

I have been working with a database that was already created by somebody else, who now no longer works here, so I cant ask any questions.

None the less, there is a table 'Ascertainment' which prior to my changes new record was saved in the Table, but when you bring up the form it was blank, the input for new record consisted of Text Boxes.

I wanted to validate the data, and changed the text boxes to combo boxes, now new record does not save in the table at all, as well as does not show up in the form. Please Help, here is the Action code to when you click the 'Done' Button.

Option Compare Database
 
Private Sub cmdDone_Click()
 
    Dim response As Integer
    Dim strLinkCriteria As String
    Dim curIDNum As String
    Dim txtRqd As String
    If IsNull(Me![txtIDNUMBER]) Then
        curIDNum = ""
    Else
        curIDNum = Me![txtIDNUMBER]
    End If
 
    strLinkCriteria = "[IDNUMBER] = " & """" & curIDNum & """"
    strFormName = "Main"
 
    If Me.Dirty = True Then
        'user has entered or made changes to data on form
        response = MsgBox("Would you like to save changes?", vbYesNoCancel)
        If response = vbYes Then
            'check for required textboxes (tagged 1 on form) without entries
            txtRqd = checkRqdEntries(Me.Name, 1)
            If Not txtRqd = "" Then
                MsgBox txtRqd & " requires data before you proceed.", vbCritical
                Exit Sub
            End If
            DoCmd.Close acForm, Me.Name
            'continue on to main page
            If intaddentry = 1 Then
                'open main page in entry mode
                DoCmd.OpenForm "Main", , , , acFormAdd
                [Forms]("Main")![IDNUMBER] = curIDNum
            'Else
                'open main page with corresponding record in contacts table
                'DoCmd.OpenForm "Main", , , strLinkCriteria
            End If
        ElseIf response = vbNo Then
            Me.Undo
            DoCmd.Close acForm, Me.Name
            'if user decides not to save new patient, switchboard will open
            'otherwise:
            'If Not intaddentry = 1 Then
                'user has chosen not to save changes made to existing patient
            '    DoCmd.OpenForm "Main", , , strLinkCriteria
            'End If
        Else
            'cancel
            strFormName = "Ascertainment"
            Exit Sub
        End If
    Else
        'no changes have been made to the data on the form
        DoCmd.Close acForm, Me.Name
        'if user decides not to save new patient, switchboard will open
        'otherwise:
        'If Not intaddentry = 1 Then
        '    DoCmd.OpenForm "Main", , , strLinkCriteria
        'End If
    End If
End Sub

[andrewdb]

Also I wanted to add that in the "Main" Form I have a button that leads to the "Ascertainment" form and here is the code to that button, just in case this helps with understanding why there is a problem.

Private Sub cmdAscertainment_Click()
    Dim curID As String
    Dim recordExists As Boolean
    curID = Me![IDNUMBER]
 
    run = MainNavigate()
    If boolCancel = True Then
        Exit Sub
    Else
        If run = False Then
            DoCmd.Close acForm, Me.Name
            Exit Sub
        Else
            recordExists = FindRecord("Ascertainment", "IDNUMBER", curID)
            If recordExists Then
                DoCmd.OpenForm "Ascertainment", , , "[IDNUMBER] = """ & curID & """"
            Else
                DoCmd.OpenForm "Ascertainment", , , , acFormAdd
                [Forms]("Ascertainment")![txtIDNUMBER] = curID
            End If
        End If
    End If
End Sub

[Scott Price]

Hello AndrewDB,

When including code in your thread, and especially when including such a long portion of code, please use the [code] tags provided, as indicated in the Reply Guidelines to the right of the Reply/Message window. They are simple to use: select your code text, then click on the # icon on menu bar of the Message window.

MODERATOR

[andrewdb]

Anyone?
Please?
Maybe?

[Scott Price]

The code you have posted doesn't have any references in it to the text boxes converted to combo boxes that you mentioned having changed. Also, there is a reference in Line 23 of your first post to a user defined function named: chkRqdEntries...

Please indicate what code you changed, and post the relevant code changes. Please also post the code for the chkRqdEntries function.

Regards,
Scott

[andrewdb]

Thats the thing, the only thing I have changed was not the actual code, but from the Form 'Ascertainment' where there were fields, I took them out and replaced them with combo boxes. Thats all, fortunately I have fixed the problem of new records not being recorded. Thats half the battle. But I still can not understand why entered records do not show up when I click on the 'Ascertainment' button from the 'Main' menu? Its just a blank form, even though the record exists in the Table.

Please Help.

Here is the code for the function you have asked:

Option Compare Database
 
Function checkRqdEntries(frmName As String, tagNo As Integer) As String
    Dim contrl As Control
    checkRqdEntries = ""
    For Each contrl In [Forms](frmName).Controls
        If TypeName(contrl) = "TextBox" And contrl.Tag = tagNo Then
            If IsNull(contrl) Then
                checkRqdEntries = contrl.Name
                Exit Function
            End If
        End If
    Next contrl
End Function
 

Thank You for your Time

[Scott Price]

What's the control source of your combo boxes? Combo boxes have both a control source and a row source.

I'm assuming you have set the row source correctly, but nothing will initially show in the combo box if you do not set the control source.

Regards,
Scott

[andrewdb]

Row Source contains the two columns that are my 'Choices' with description that I want to display, and the corresponding code number that I want to store in the table.

Control Source is the reference to the variable/column where the information is being stored in the table. In one of the cases for example I have a combo box for 'Referred by' which is stored in my 'Ascertainment' table under variable REFDBY, which is the control source for that combo box.

So it does seem like there is reference to it, I just don't understand why it wont display, very strange.

Andrew

[missinglinq]

Option Compare Database
 
Function checkRqdEntries(frmName As String, tagNo As Integer) As String
    Dim contrl As Control
    checkRqdEntries = ""
    For Each contrl In [Forms](frmName).Controls
        If TypeName(contrl) = "TextBox" And contrl.Tag = tagNo Then
            If IsNull(contrl) Then
                checkRqdEntries = contrl.Name
                Exit Function
            End If
        End If
    Next contrl
End Function
 

I'm not still clear on exactly what you're doing here, but in Line # 7 of the above code you have a line

If TypeName(contrl) = "TextBox" And contrl.Tag = tagNo Then

Since you've converted some textboxes to comboboxes you may need to modify this code to to include comboboxes.

Linq ;0)>

[andrewdb]

How would I do that?

Would it be like this?

If (TypeName(contrl) = "TextBox" OR "ComboBox") And contrl.Tag = tagNo Then

?

[missinglinq]

No, when using And/Or you have to include the complete evaluation expression

 If (TypeName(contrl) = "TextBox" OR TypeName(contrl) = "ComboBox") And contrl.Tag = tagNo Then

Linq ;0)>

[andrewdb]

I have tried that as well, nothing changes. Still the when I click on the button the Form comes up with empty cells/combo boxes. How strange! WHY? Anybody?