Combobox entry to invoke another form

[VaughnIT]

I have a form with a combobox (Candidates) and want to be able to add a new name if not on the list . How can I have the form to enter new Candidate be invoked once I type in a name in the combobox. I need to invoke the form because inserting the name directly in the Candidate table would be incorrect because there are other fields to be also filled in on that form such as candidate number. I don’t want to have records in the candidate table with just Names with all other fields empty.
Does anyone know how to implement this?

 

[BuckRodgers]

You could add code like this to the Not In List Event for the combo box...

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

Const DataDesc As String = "Candidate"
Dim strMsg As String
Dim intNewEntry As Integer, strTitle As String, intMsgDialog As Integer

' Display message box asking if user wants to add a new value
strTitle = DataDesc & " Not In List"
strMsg = "'" & NewData & "' is not in the list. " & Chr(13) & Chr(10)
strMsg = strMsg & "Would you like to add it?"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewEntry = MsgBox(strMsg, intMsgDialog, strTitle)

If intNewEntry = vbYes Then

DoCmd.OpenForm "Candidates", acNormal, , , acFormAdd, acDialog, NewData

' Continue without displaying default error message.
Response = acDataErrAdded

End If
End Sub

 

[VaughnIT]

I had a table with 13 records and manually deleted the 10th record from the table. The first field PersID is of type Autonumber. How can I get that be reordered so the PersIDs are numbered from 1 to 12 consecutively rather than having PersID 10 missing.
I need this because right now the database has all dummy test data... but when I'm done I'd like the user to be able to load their own data and be able to have the PersIDs loaded and numbered consecutively starting at 1.
Can anyone explain how to get that done and if this requires some VBA code please elaborate on what it is and where it goes.

Thanks in advance

 

[PHV]

Do a keyword search in the different Access fora for autonumber.
Why do you want a consecutively numbered PersID ?
What is the matter of missing PersID=10 ?
Anyway, if PersID is a Foreign Key elsewhere in your database you'll have a lot of work renumbering ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244