I have a form that opens using Initialise shown with code below...


Sub Initialise(intNHSNo As Long)
'set form recordsource to selected form number
Dim sQRY As String
sQRY = _
"SELECT jez_SWM_Visits.VisitID, jez_SWM_Visits.NHSNo, jez_SWM_Visits.Surname, jez_SWM_Visits.Forename, jez_SWM_Visits.Gender, " & _
"jez_SWM_Visits.Address1, jez_SWM_Visits.Address2, jez_SWM_Visits.Address3, jez_SWM_Visits.Postcode, jez_SWM_Visits.Telephone, " & _
"jez_SWM_Visits.DateOfBirth, jez_SWM_Visits.ReferralReasonDescription, jez_SWM_Visits.SourceDescription, jez_SWM_Visits.DateOfReferral, " & _
"jez_SWM_Visits.VisitDate, jez_SWM_Visits.OpenorClosed, jez_SWM_Visits.Weight, jez_SWM_Visits.Height, jez_SWM_Visits.BMI, " & _
"jez_SWM_Visits.BloodPressure, jez_SWM_Visits.ExerciseLevel, jez_SWM_Visits.DietLevel, jez_SWM_Visits.SelfEsteem, " & _
"jez_SWM_Visits.WaistSize, jez_SWM_Visits.Comments, jez_SWM_Visits.SessionType, jez_SWM_Visits.NHSStaffName, " & _
"jez_SWM_Visits.Arrived, jez_SWM_Visits.ActiveRecord, jez_SWM_Visits.InputBy, jez_SWM_Visits.InputDate, jez_SWM_Visits.InputFlag " & _
"FROM jez_SWM_Visits " & _
"WHERE jez_SWM_Visits.NHSNo = " & intNHSNo
With Me
.RecordSource = sQRY
.txtNHSNo = intNHSNo
.txtDummy.SetFocus
End With
End Sub


I also have another form which is a Search Form. When I have found the person I want to Search I have either elect the line and Single Click cmdButton or Double Click on the line, as shown in the code below

Private Sub cmdShowSearch_Click()
Dim sQRY As String
If IsNull(Me.lstSearch) Then
MsgBox "Select from the list", vbExclamation
Exit Sub
End If
sQRY = "INSERT INTO jez_SWM_UsersLog ([UserName], [RequestDate], [RequestType], [NHSNo]) " & _
"VALUES ('" & fOSUserName & "', '" & VBA.Now & "', 'SearchInputRecord', '" & Me.lstSearch & "') "
DoCmd.RunSQL sQRY
Form_frmVisits.Initialise Me.lstSearch
Form_frmSearchDetails.Visible = False
End Sub
Private Sub lstSearch_DblClick(Cancel As Integer)
Call cmdShowSearch_Click
End Sub


My problem is that when I select the record I want I get the error message 'Overflow' and cant get the main form to be populated by the search.

How can I do this?

Howzit

I come across overflow issues when trying to fit a value into a field that is too small to hold it - i.e. trying to put 50000 into an integer field (only holds integer values between -32768 and 32768)..

It may be the date part of the code - see below.

I would also check the datatype and the field sizes of both the target tables as well as what you want to insert into the table - to ensure they are the same.





Private Sub cmdShowSearch_Click()
Dim sQRY As String
If IsNull(Me.lstSearch) Then
MsgBox "Select from the list", vbExclamation
Exit Sub
End If
sQRY = "INSERT INTO jez_SWM_UsersLog ([UserName], [RequestDate], [RequestType], [NHSNo]) " & _
"VALUES ('" & fOSUserName & "', #" & VBA.Now & "#, 'SearchInputRecord', '" & Me.lstSearch & "') "
DoCmd.RunSQL sQRY
Form_frmVisits.Initialise Me.lstSearch
Form_frmSearchDetails.Visible = False
End Sub
Private Sub lstSearch_DblClick(Cancel As Integer)
Call cmdShowSearch_Click
End Sub