Multiple source for combo box 2

[Neowynds]

I am creating a Security Incident Report database (similar to a police report) I have a combo Box in Table1 and under the field [Report Subject:] I have a combo box. My goal is to get the combo box to display the list of subjects in the table [ReportSubjects] as well as add any new data to the drop down box when the user is filling out the form Which was created from Table1. I have the combo box linked to the second table, and it displays the list of Subject Reports my problem is....Is it possible to have user entered data in the [Report Subject:] field using Table1 fields to update in the ReportSubjects list of subjects? I cannot possibly enter all possible subjects that a report may be written about, so it would be easier if the list would self update if a user entered in a subject not on the list already. If this was a bit confusing I have been working on this database for 24 hours straight. Please Help. If any clarification is neccessary I will try, just ask me a question.

Also in case it wasnt clear enough (I am usually confusing to others,especially when tired)

The user will Enter data into a form which I created using a wizard off of Table1. The combo box for Report Subject is based on the table ReportSubjects. Can I have data the user types in the combo box in the form that is not already on the list added to the ReportSubjects table, even though the form is based off of Table1?

(The Sleep Deprived) Neowynds

 

[daklem]

Updating a combo box which is populated from a table can be done with the NotInList event procedure. First make sure the Limit To List property of the combo box is set to True. This allows the NotInList event to be functional.

Create an event procedure in the NotInList event of your combo box (I called the combo box cboReportSubject) and include the following code.

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

Dim db As Database
Dim rs As Recordset
Dim strMessage As String

strMessage = "Would you like to add " & NewData & " to the list?"

If MsgBox(strMessage, vbQuestion + vbOKCancel) Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReportSubjects&quot
rs.AddNew
rs!Subject = NewData
rs.Update
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

End Sub

Substitute the name of your combo box for the first red statement, and the name of your table the combo box is populated from for the second red statement.

This procedure will prompt the user to confirm they want to add the new report subject they typed in the combo box to the list, and it will update the list.

Good luck!

 

[BillPower]

Hi daklem,

I had a look at your code because it's shorter than what I usually use. But I couldn't get it to work, still adds the NewData if cancel is selected. This is what I usually use/suggest.

Dim dbs As Database, rst As DAO.Recordset, strMsg As String, strTitle As String
strTitle = "Report Subjects List"
strMsg = "Would you like to add " & NewData & " to the " & strTitle & "?"
Response = MsgBox(strMsg, vbOKCancel, strTitle)
If Response = vbOK Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ReportSubjects&quot
rst.AddNew
rst![Report Subject] = NewData
rst.Update
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If

My sincere apologies if I am doing something wrong though with your code. let me know. Thanks.

Bill

 

[daklem]

Thank you billpower for the welcome and for catching my mistake. You are right, the combo box entry adds to the list when Cancel is chosen.

I generaly use a generic function for the message box. In this case I was trying to put it all in the same procedure and ended up with the error you pointed out.

Thank you for bringing it to my attention.

 

[Neowynds]

I tried both sets of code and I get an error message

Debug has this section highlighted

Dim dbs As Database

And the error message I get is:

Compile Error:
User-defined type not defined

What does this mean?

I got this error with both codes posted, this is what I currently have in:

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

Dim dbs As Database, rst As DAO.Recordset, strMsg As String, strTitle As String
strTitle = "Report Subjects List"
strMsg = "Would you like to add " & NewData & " to the " & strTitle & "?"
Response = MsgBox(strMsg, vbOKCancel, strTitle)
If Response = vbOK Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ReportSubjects&quot
rst.AddNew
rst![Subject] = NewData
rst.Update
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If



End Sub

(confused)Neowynds

 

[BillPower]

Hi Neowynds,

Sounds like you're using Access 2000 or greater and need to install a reference to "Microsoft DAO".

Open the Visual BAsic Editor, from the menu select Tools> References, scroll down until you can see Maicrosoft Data Access Objects 3.x, tick/check the highest version no. Should solve the problem.

Bill

 

[daklem]

Make sure you have a reference to the DAO object library in your database.

While in the Visual Basic Code Editor click Tools then References. In the References dialoge box scroll down to Microsoft DAO 3.6 Object Library and click the box to place a check mark. You may also want to remove the checkmark from the ADO reference.

Then use billpowers code.

Good luck!

 

[Neowynds]

SUCCESS!!!! Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, Thank you, I have been struggling with this for 12 hours. You all are life savers!!!!!!!



(Very Relieved)Neowynds

 

[Neowynds]

OK ran into a small problem.

It now shows a popup asking if I want to add it to the list however when I click ok it says that it is a duplicate and that is not allowed. tblReportSubjects allows duplicates so does tblTable1, also when I click ok it adds the word to the list, but gives me the above error. the cancel button works fine but if I pick anything off the dropdown list it also gives me the same no duplicates allowed error.

Please help.

Neowynds

 

[BillPower]

Hi Neowynds,

I'm thoroughly lost with this and can't make any suggestions without viewing the Form and Tables.

You are welcome to send me a copy of your DB, removing any sensitive records first. billpower@cwcom.net

Will post any suggestions ASAP in this thread.

Bill

 

[Neowynds]

Thanks anyways,

I figured it out I had some of the other fields set to not accept duplicates and I was testing the Report Subject field and it was just filling the other fields with nothing so I could run the test data once but as soon aI tried to make another entry at all it gave me those errors. I changed the indexes to allow duplicates and it is running smoothly now. I am going to start another thread however on different part of this database maybe you could help since you are already familiar with the premise and use.
Thank you again.

Neowynds