Filter vs Find ?

[Huddle]

Reading other posts I've got bits and pieces of what I want to do but can't figure out the easiest most user friendly way to do it.

I have a primary field named blgd#. When a user is entering data the building number may already be listed in which case they would get the "change not successful because it would create a duplicate" message. They will have no idea what that means.

So I'm not sure if I could

• put a filter on the form so as they type the building number in the field the record will come up in a list, (I think would be most user friendly but by other posts it sound very complicated to do)
• or have a pop up form where they enter the building number which runs a query that would look to see if the number exists, (I would think with this you would have to have an if statement so if there is no record it opens a blank but if there is it opens that record)
• or when there is a duplicate have the Find option pop up,
• or have Find pop up when the form is open.

There would be too many records to have them scroll through the whole list to see if the building number exists already.

Any suggestions on what would be best or if there is a better way to do it?

[dsmacs]

If you include a combo box with the building numbers as the underlying query, as the building number is entered it will narrow down to the number. If the number is not in the list you can use the NotinList event to advise the user "No such building number, do you want to add Y/N?".

Private Sub CboBuildNo_NotInList(NewData As String, Response As Integer)

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

On Error GoTo Err_BuildID_NotInList
' Exit this subroutine if the combo box was cleared.

If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new building.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a message, set the Response argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new Building, open a recordset using the Building table.

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblBuilding", dbOpenDynaset)

' Create a new record.
Rs.AddNew
' Assign the NewData argument to the OutletMessage field.
Rs![blgd#] = NewData
' Save the record.
Rs.Update
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If


Exit_BuildID_NotInList:
Exit Sub
Err_BuildID_NotInList:

' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue
End Sub



You then would open a Building Form at the Desired new record and have the user enter additional details required about the building?



Hope this helps,
Cheers
Darren

[Huddle]

This seems to be what I would need, however I've never worked with code before only formulas and micros.

If there is a similar way to accomplish this without code can you direct me. If not I will see if I can learn how to do this.

Thanks so much.

[dsmacs]

Email me a sample of your DB with some dummy data and I'll place the code in and provide info on what the code is doing?

d [dot] mcdonald @ ozemail [dot] com [dot] au

[Huddle]

I have attached the dummy database you requested. Let me know if you need any addtional information.