Microsoft Access can't set the LimitToList property to No right now.

[DavidAustin]

Hi all,

I am making a new database with a data entry form as standard. One of my tables is tblFile which is where the entered data will fill. Another table I have is tblLocation, which is a list of Locations where the files have come from.

On my data entry form for new records to the tblFile, I have a combobox that uses the tblLocation fields (LocationID as a primary key and FileLocation as the name of the location) as a row source to populate the control source of LocationID in tblFile. When users will enter new records, they may need to add a new location so I want to set the LimitToList property to No. However, the error message below comes up:

"Microsoft Access can't set the LimitToList property to No right now. The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first, and then set the LimitToList property."

Essentially is there a way for the user to enter a location (e.g. London) and if it is not in the list add a new record to the tblLocation and then update the new record in the tblFile?

Many thanks in advance!

[Seth Schrock]

I have come up with a fancy way of doing just that. First, you will need to create a form that is bound to tblLocation, has a close button, and has its Modal property set to Yes. Then, in your Row Source property for the combo box, make it a UNION query, like this:

SELECT LocationID, FileLocation FROM tblLocation
UNION
SELECT 0, "<Other>" FROM tblLocation
 

Now, create an After_Update event for your combobox that checks if the value equals 0, then open the form you created as a dialog box then requery your combobox, and then set the value to whatever value is in the global variable that you populated in the next step. In frmLocation's close button Click event, set the value that you just entered into a global variable and then close the form. Because frmLocation is Modal and opened as a dialog box, the code in your combobox's AfterUpdate event won't continue until the form closes.

[DavidAustin]

Wow, thanks for the quick response. I'm still a novice with all this stuff so can I make a step by step style list and get you to check it before I give it a go?

1. Create a new blank form bound to tblLocation and set its modal property to yes - what do I put on the form? is it blank?
2. Change the Row Source property of the combobox on the data entry form to the union query you stated above - I don't quite understand the select 0 line though!
3. After_Update event of combobox to something like

If Me.value = 0 Then DoCmd.OpenForm(Newform)

4. What do you do once you've opened the form? Does it get populated by the combobox somehow?
5. Requery the combobox - how do you do this?
6. "Set the value to whatever value is in the global variable that you populated in the next step" - this is populated by the new form?
7. Change the close button click event to populate the global variable and then close

So does this add the new record to the tblLocation and then performs the after update event of the combobox?

Sorry for all the questions!

[Seth Schrock]

1. You would put all the controls necessary to be able to add a new location.

3. Change the code to

If Me.Combobox_Name = 0 Then
   DoCmd.OpenForm FormName:="NewForm", WindowMode:=acDialog
   Me.Combobox_name.Requery
 
   'Set the combobox to the new location
   'glNewLocation is the global variable that you need to create
   Me.Combobox_name = glNewLocation
End If

4. Once the form opens, you add the new location and then click the close button.

5. Code added in step 3

6. Code added in step 3

New Step 8. In your new form's OnLoad event, add the following:

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Leave this code as is. Don't change any names in it.

This should do it.

[DavidAustin]

So I've made the new form, set it to modal, put the code in 8 above into the OnLoad event and in the OnClose event made the global variable equal the textbox.

I also put the global variable in a new module as a string, is this correct?

In the data entry form, I have put the code in 3 into the AfterUpdate event (and changed the combobox_name to its actual name!).

I'm getting an error though when I try out the new form:

"The expression After Update you entered as the event property setting produced the following error: A problem occured while Microsoft Access was communicating with the OLE server or ActiveX control."

Any idea what's going on here?

[Seth Schrock]

Please post exactly what you have in the AfterUpdate event, including the Private Sub/End Sub parts (using Code tags).

Also, what is the error number that you are getting?

[DavidAustin]

I've redone the whole form and using your method, with a slight change, I've managed to make it work!

On the data entry form, the new Combobox is called cmdLocationID. The "NewForm" is now "frmLocation" and glNewLocationID is the global variable which stores the ID number of the new location. I've made the first ID of the locations be stored as <Other>, so when the user selects <Other> it opens frmLocation. In the AfterUpdate event of frmLocation, the ID number is stored in glNewLocationID which is then used to change the value of the combo box on the data entry form

Private Sub cmdLocationID_AfterUpdate()
If Me.cmdLocationID.Column(0) = 1 Then
    DoCmd.OpenForm "frmLocation", , , , , acDialog
    Me.cmdLocationID.Requery
    Me.cmdLocationID.Value = glNewLocationID
End If
End Sub

Private Sub FileLocation_AfterUpdate()
glNewLocationID = Me.LocationID
End Sub

Many thanks for all your help!

[Seth Schrock]

Like I said, it may not be the easiest to design, but I really like this method for the user. It is really easy to explain how to do it to the end user.

Good luck on your project.

[DavidAustin]

It's a little awkward but does the job unlike how the program wants it to happen so it's all good with me! Thanks for all the help, no doubt I'll be back again with more questions :)