Help With Code Please

[Flightdisc]

Hi,

I have some code that is used in the On Not In List and allows you to add
data to an existing list. It works great but I would like to modify it so I
can pass data to it from other form controls. The link to original code was
passed to me by John Vinson MVP (god bless him)
http://www.mvps.org/access/forms/frm0015.htm

I currently have four combo controls each with a populated list. I would
like to use the code below in each combo, but it seems like overkill having
four instances of the same code. I thought being able to pass values to it
would be a better approach, but I don't know how to set that up. Thanks in
advance.

Here's the code:

Private Sub txtCompany_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Name to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Companies, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Company_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[Tom van Stiphout]

On Tue, 16 Jun 2009 18:16:01 -0700, Flightdisc

Do you mean IDENTICAL code? So you have 4 dropdowns showing
CompanyNames? That seems unusual but OK.
Here is how to do it:
Cut the code from the procedure and put it in a new Private Function
in the same code module:
Private Function CompanyNotInList(NewData as String) as Integer
We return an integer that will later be used for the Response argument
in the NotInList events.
So the only line in each of the NotInList events will be:
Response = CompanyNotInList(NewData)

Then in the body of the new function paste your code.
At the top of that function above the pasted code declare the Response
variable:
Dim Response as Integer

At the bottom of that function write one line of code to return
Response:
CompanyNotInList = Response

-Tom.
Microsoft Access MVP

 

[Graham Mandeno]

Hi Flightdisc

Yes, you can write a fairly general-purpose function for this. Assuming
your different combo boxes are populated from different tables, you will
need to pass the names of the table and the field to your function, along
with the NewData string. The function will need to return the Response
value:

Public Function GeneralNotInList( _
NewData as String, _
TblName as String, _
FldName as String) as Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
On Error Goto ProcErr
strMsg = "'" & NewData & "' is not an available Name " _
& vbCrLf & vbCrLf _
& "Do you want to add the new Name to the current list?" _

& vbCrLf & vbCrLf _
& "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
GeneralNotInList= acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(TblName, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs(FldName) = NewData
rs.Update
GeneralNotInList= acDataErrAdded
End If

ProcExit:
If Not rs Is Nothing then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

ProcErr:
strMsg = "An error occurred." _
& vbCrLf & vbCrLf _
& Err.Description
& vbCrLf & vbCrLf _
& "Please try again."
MsgBox strMsg, vbExclamation
GeneralNotInList= acDataErrContinue
Resume ProcExit

You can then call it like this from each of the *real* NotInList event
procedures:

Private Sub txtCompany_NotInList(NewData As String, Response As Integer)
Response = GeneralNotInList(NewData, "Companies", "Company_Name")
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

[Posted this earlier but it got deleted from the server - trying again]

Hi Flightdisc

Yes, you can write a fairly general-purpose function for this. Assuming
your different combo boxes are populated from different tables, you will
need to pass the names of the table and the field to your function, along
with the NewData string. The function will need to return the Response
value:

Public Function GeneralNotInList( _
NewData as String, _
TblName as String, _
FldName as String) as Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
On Error Goto ProcErr
strMsg = "'" & NewData & "' is not an available Name " _
& vbCrLf & vbCrLf _
& "Do you want to add the new Name to the current list?" _

& vbCrLf & vbCrLf _
& "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
GeneralNotInList= acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(TblName, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs(FldName) = NewData
rs.Update
GeneralNotInList= acDataErrAdded
End If

ProcExit:
If Not rs Is Nothing then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

ProcErr:
strMsg = "An error occurred." _
& vbCrLf & vbCrLf _
& Err.Description
& vbCrLf & vbCrLf _
& "Please try again."
MsgBox strMsg, vbExclamation
GeneralNotInList= acDataErrContinue
Resume ProcExit

You can then call it like this from each of the *real* NotInList event
procedures:

Private Sub txtCompany_NotInList(NewData As String, Response As Integer)
Response = GeneralNotInList(NewData, "Companies", "Company_Name")
End Sub

 

[Flightdisc]

Graham,

I can see from looking at your code that this is just what the doctor
ordered. I certainly do appreciate your help with this as I could not have
accomplished this on my own. Awesome work my friend.

Tom,

Also wish to extend my gratitude to you for your response and solution. My
apologies for not mentioning the type of data in each combo box. Just for
clarification though, the combo's list companies, contact names, parts, etc.

Again, thank you both for your input and guidance. I have learned something
new thanks to you.

FD

 

[Flightdisc]

Tom, Graham, Mike,

Sorry for taking so long to respond back. I looked at all suggested
approaches but settled on Graham's approach since I didn't need to write
anything on my own, thank god since I don't know how to do that yet.

Anyway, I just pass a field name and table name of the table I want to add
new data too and it works great. Sample follows:

Response = GeneralNotInList(NewData, "Contacts", "Contact_Name")

I want to thank each of you for your help on this. I'm deeply grateful.

-FD