Combo Box LimitToList Problems

[Gerryw]

Hi All,
I have a form with a number of fields into which i want to be able to pick from a list of existing records and also be able to enter data to be saved as new.

The Form is basically for inputting Date, Tutors Names, Work Day Names, Course Name, Class Name, Course Level, Hours Worked, Total Hours.

When I try to set the LimitToList in the combo box properties I get an error saying;
Access can'nt set the LimitToList Propery to right now.

The first visible column, which is determined by the ColumnWidths Propery, is'nt equal to the Bound Column. Adjust the ColumnWidths Propery first, and then set the LimitToList Property.

I alter the 0cm in the ColumnWidths propery to 1cm and then set the LimitToList propery to NO. This is allowed, but, when I open the form and try to enter data into the TutorsName field (Plus all the others) I get the TutorsID numbers i.e. 1 2 3 4 etc first and then the names. The field will only allow numbers.

I think that all but just to get this correct first of all will help greatly.
Thanks alot.
Gerry

 

[suepowell]

Hi Gerry

Have you tried changing the column with of the id column back to 0cm once you have set the limit to list property to yes.
This might work.

I know I have loads of combo coxes with limit to list set to yes and the bound column not visible so what you are trying to do is possible.
Once you have got that bit working, I use the not in list event of the combo box to open the form used for inputting the data for the combo box .

If you need more help I will have a look for an example or 2 in the morning if you have not solved it by then.

Sue.

 

[decie]

Did you try changing your Bound Column to the first visible column? For example, first column(not visible) 1, second column (visible) 2;
Column Count: 2
Column Width: "0"; "1"
Bound Column: 2


Here's an example from one of my applications. The user gets inquiries from various newspapers and has to track them. She occasionally has to add a new one to her list as she is keying the record.

The combobox:
Row Source: consists of the media table with multiple fields, however now she's only interested in 1 field, so that's all that is displayed in the query.
Column Count: 1
Column Width: .5
Bound Column: 1
Limit to List: Yes

Event
OnNotInList: [Event Procedure]
Private Sub MediaBusName_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me.MediaBusName, NewData)

The following public function is placed in a module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table

Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource '(same as Me.MediaBusName)
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
Resume Exit_Append2Table
End Function

Hope this helps.

 

[suepowell]

Gerry

Due to the fact I was reading your message late last night, I misunderstood what you wanted.

A couple of questions.

1 Is the data for the combo box obtained from a table.

2 Do you need to save more than 1 piece of data from each new input to the combo box.

If you need to save more than 1 piece of data when a new choice is entered, you will need to do this through an input form of some sort.

What I have done is to set the limit to list to yes ( which you have found you have to do anyway) and on the notinlist event of the combo box put something like the following

Private Sub Customer_NotInList(NewData As String, Response As Integer)
If MsgBox("This Customer has not yet been entered, do you want to add them?", vbYesNo, "Add a new customer?") = vbYes Then
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmcustomer"

DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, Customer
Response = acDataErrAdded
Else
Response = acDataErrContinue
MsgBox "Choose a customer from the list"

Exit Sub
End If

End Sub

If you will ever want to produce a report based on the values of this combo box, you should set limit to list to yes, otherwise you risk lots of mis-spellings and several sections in your report for the same real life section.

I hope this helps, but do get back to me if you need more info.

Sue

 

[fearoffours]

The following public function is placed in a module:

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table

[COLOR="Red"]Dim rst As DAO.Recordset[/COLOR]
    Dim sMsg As String
    Dim vField As Variant       ' Name of the field to append to.
.....

Hi I have an identical problem to the OP. I've tried using this code, but get a "Compile error: user-defined type not defined" on the highlighted line of code above. Any ideas? I'm using Access 2000

 

[fearoffours]

Solved using alternative code, found here.