Using a textbox control to check to see if a record exists in a table

[ssanfu]

Is "AssessionNumber" a number or text as defined in the table?

[KerryAustin]

Thank you for the example. I set up a new combobox to look up the Assession number in the list. That appears to work fine. I don't understand the Me.Filter. How do I get the record to populate back to the form for editing. For example my table tblDelay has the following fields (dept, Rm_Num, MRN, AssessionNumber) associated form textboxs (cboDept, cboRmNum, txtMRN, txtAssessionNumber). I I can get a few to post back to the form I can probable work on getting all of them If I can get an example.

Thank you for your help.
Kerry

[KerryAustin]

AssessionNumber is a number field Long Integer General Number

[June7]

The Me.Filter filters the form recordset to display the record that has the assession number.

[KerryAustin]

So was this to populate the form with the recordset. If so, It did not populate.

[June7]

If the form is bound to the table with AssessionNumber field, the filter should work.

Post code or provide db for analysis.

[Missinglinq]

I don't think anyone has asked this question, as yet: Is [AssessionNumber] actually defined as a Number Datatype Field in the underlying Table? Or could it be, as is often the case with 'numbers' that consist of digits but are not used in math, actually defined as a Text Datatype?

Linq ;0)>

[June7]

Asked in 16 and answered in 18.

[KerryAustin]

My database has some private information so I can't send. Here is the code:

Private Sub cboAssessionSearch_AfterUpdate()
If Not IsNull(DLookup("AssessionNumber", "tblDelay", "AssessionNumber=" & Me.AssessionNumber)) Then
'code to filter form for the existing record
Me.Filter = "AssessionNumber=" & Me.AssessionNumber
Me.FilterOn = True
Else
'code to move to new record row
DoCmd.GoToRecord , , acNewRec
End If
End Sub

[June7]

I don't see anything wrong with code - it's exactly what I suggested.

[Missinglinq]

Asked in 16 and answered in 18.

I need to back off of my medication or maybe increase it!

[KerryAustin]

June7, so this works partially. When I select a Assession Number for the combobox I get a Run-time error '3022'. It stats in part ...will create duplicate values in the index. I also have cboDept as a required field. It sends Run-time error '3314' Must enter a value in the tblDelay.Dept field. Can I add a error handle for Dept?

Thanks,
Kerry

[KerryAustin]

I forgot to mention that once I end the debug, the Assession number is populated in txtAssessionNumber field on the form.

KA

[June7]

Not sure why that duplication error happens. If you want to provide latest version, will review.

If field in table is defined as required, then data must be entered. I don't know if that message can be captured by error handler because it really isn't a run-time error - it's not triggered by bad code. I don't often set a field as required. I deal with data validation programmatically.

[Missinglinq]

...When I select a Assession Number for the combobox I get a Run-time error '3022'. It stats in part ...will create duplicate values in the index....

It sounds as if the Assession Number Combobox is Bound to the Field in the underlying Table; for doing this kind of thing (if I'm understanding what you're trying to do) the Combobox has to be Unbound.

... I don't often set a field as required. I deal with data validation programmatically...

...I don't know if that message can be captured by error handler because it really isn't a run-time error - it's not triggered by bad code...

I never set a Field as Required, either, but it can be trapped. If Dept is the only Required Field this will do it:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3314 Then 'Required Field has been omitted
  
 Response = MsgBox("Department is a Required Field!", vbExclamation, "Please Select Data For This Field")
 Response = acDataErrContinue
 cboDept.SetFocus

End If

End Sub

Linq ;0)>