Create new record if filter returns no record

[mb88]

Hi all,

First up - I'm brand spanking new to the database and coding world, so the complete and utter dumbing down of any responses would be awesome.

I got the code below from the web. The goal is to filter the records in qryNeighbours by the burn number that is selected in cboBurnNumber. This code worked for a dataset in which all of the possible burn numbers are present. My problem is that the qryNeighbours data set does not yet contain any data - the idea being that people will select a burn number then input into a new record - and as such (I'm assuming) is spitting out an error because the filter can't find a match.

Can anyone offer any suggestions?

------------
Sub SetFilterNeighbours()

Dim LSQL As String



LSQL = "select * from qryNeighbours"
LSQL = LSQL & " where BurnNumber = '" & cboBurnNumber & "'"

Form_frmNeighbour.RecordSource = LSQL
-------------


End Sub

Cheers!
Michael

[June7]

Try:

With Me.RecordsetClone
.FindFirst "BurnNumber='" & Me.cboBurnNumber & "'"
If .NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = .Bookmark
End If
End With

[mb88]

Thanks for the reply!

Again, completely new to this - Is this intended to replace the code I have, or work with it?

Just to further explain in case it changes the scenario;
I have a number of forms in a navigation form. I want all of them to link to the combo box in the header which acts as the filter for all forms (see attached image)

[June7]

It was intended to replace.

However, use of navigation form might complicate. I don't like and don't use navigation form. It confuses me.

[Gambit17]

Yeah I am also Brand Spanking new to MS Access and would also like to implement something similar. Whereby when entering information into a form, the form checks if it is duplicate info, tells the user if it is and takes them to the original form. Is this possible?

Sorry for Hijacking your post mb88

Thanks

[June7]

Yes, that is possible, perhaps with code similar to already suggested which uses unbound controls to enter search criteria or with code in a combobox NotInList event. All depends on individual situation.

[mb88]

That's cool Gambit17!

Thanks for the suggestion June7,