Form to open with no records....

[Ashfaque]

Hi,

I have a single form (which display patients info) where I am receiving data thru a query called Q_Patient. All fields in form's detail section are bound with query fields.

I have placed some 2-3 unbound text box in form header section named below;

TxtP_Code
TxtPName
etc...

Thru the above unbound text boxes I am receving particular patients as per my requirements. AfterUpdate event of the above unbound text box, I called below function to display particular data. Example if I type patient code and press enter, then particular records are appearing.....
Untill here it is fine.

Once I open the form thousands of records are displaying at first time due to query is record source form. Then if I type patient code then that particular patient will apprear only and load on form will reduce.

What I want is; once the form is open "No records" should be displayed.....

Function SearchP()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.TxtP_Code) Then
strWhere = strWhere & "([P_Code] Like ""*" & Me.TxtP_Code & "*"") AND "
End If

If Not IsNull(Me.TxtPName) Then
strWhere = strWhere & "([PName] = """ & Me.TxtPName & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Nothing."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If

End Function

ANY IDEA ?

 

[Scooterbug]

on the On Load event for the form:

docmd.GoToRecord ,,acNewRec

 

[missinglinq]

You can do this by moving to a new record on opening the form. No new record will actually be created unless the user enters data, and you can then use your search function to pull up the desired record.

Private Sub Form_Load()
 DoCmd.GoToRecord , , acNewRec
End Sub

 

[tomocb]

I think both you and I are after the same thing. I've just started a thread which provides what you are after. However, I'm having problems getting it to work.

The logic for it is correct, it's kust my understanding of the syntax required to get access to work properly.

Basically, I'm using a Data Entry = Yes method On Open to stop all of the records from first being displayed.

Once filter information has been supplied in the unbound fields the data entry is to No. However, the requery is not working properly.

Check my thread for a response or I will let you knwo as soon as I have a solution.

 

[Mr. B]

The major flaw to the type of solution that has been proposed so far is that in each case a new record is being created. If this were my applicaiton, I would not want to have to manage new records all the time.

I would propose that you leave the Record Source for you form blank, which will cause it to be blank when opened (no records) not even a blank one. At this point you may even want to have the Visible property of the Details section of your form set to False.

Then create the entire Sql statement using your VBA code, not just the Where part. Then just make the sql statement that your VBA code is generating the record source for your form.

All you would need then is a button to allow for the creation of a new record. At this point you can either make the entire table the record source before adding the record or you can just use your form in an Unbound fashion where you collect all the info and then us DAO to open a new recordset and add the new record.

HTH

 

[Ashfaque]

Thanks Gents.

Since this form is only to search / view records only hence the form have below setting
Allow Filters = Yes
Allow Edit= Yes
Allow Delition = No
Allow Addition = No
Data Entry = No


If I use
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

While opening form it displaying msg "Run-time erro 2105... You can not go for specified record"

Please advise.....

Thanks in advance

 

[Mr. B]

You stated "this form is only to search / view records only" and you have the correct settings for that statement. Then later you have the "DoCmd.GoToRecord , , acNewRec" statement. You have already set the Allow Addition = No so that is why you are getting the error.

Try first setting Allow Addition = Yes and Allow Edits = Yes before you try to go to a new record.

 

[Ashfaque]

Doctor Access,

You are correct...I changed it to
Allow Addition = Yes
Allow Edits = Yes

And added missinglinq's idea of additing new record. Thanks missinglinq.....

it works now.....Thanks a lot gents....

With kind regards

 

[Mr. B]

You are welcome. Glad to help.

 

[Ashfaque]

Thank you Doctor B,

Can you look at my this post....May be you or missinglinq can help me..

http://www.access-programmers.co.uk/forums/showthread.php?t=172361

I am still waiting for solution....