Combo box as Data input tool

[Uaikega]

I am writing a library (as in room full of books and things) application - client has everthing else in Access, so it is appropriate that this application is too. I am not experienced in Access and am finding it very fiddly!

The situation is that I want to use combo boxes for the input of fields that are the same over a large number of records - in this case the "collection". Most often the user simply selects a value from the list in the combo box (this bit works perfectly). Occasionally (s)he will need to add a new value, as the required value is not on the list. I want this new value to be added to the table the combo box is using, to be visible in the combo box, and to added to the data record.

Because the field in question is either "one-to-many" or "many-to-many", and because it needs to be carefully controlled we have quite an intricate network of linked tables involved. Access seems to be coping with all this fairly painlessly - until we reach this point!

Code "behind" the combo box in question :

Private Sub Collection_edit_NotInList(NewData As String, Response As Integer)
'Routine to handle case where user attempts to enter a new "Collection" name
On Error GoTo Err_Collection_edit_NotInList
NewData = Capitalise(DataTrim(NewData))
Response = acDataErrContinue
If MsgBox("'" & NewData & "' is not an existing collection. Would you like to add this collection now?", vbYesNo + vbQuestion + vbDefaultButton1, "Collection not found...&quot = vbNo Then
Me.Collection_edit.Undo
Me.Collection_edit.Dropdown
Exit Sub
Else
DoCmd.OpenForm "Add a new collection", , , , acFormAdd
Forms![Add a new collection].[collection_name] = NewData
newcode = Code3letter(NewData, "&quot
Forms![Add a new collection].[collection_ID] = newcode
Stop
'Me.Collection_edit.Undo
'Forms![Add a new collection].SetFocus
'DoCmd.Close , , acSaveYes
End If

This works nicely down to the "stop" - the remmed lines following are attempts of mine to close off the routine satisfactorily - the new data has been added to the table, but does not appear in the combo box, is not available for selection, and the original form seems to be pointing to a different record!

("Capitalise" is a function that puts everything into lowercase except for the first letters of words other than ones like "and", "or", "of" etc. - "Code3letter" is a function to generate a unique ID for the new item - they work fine.)

This is driving me even more crazy than I already was.

 

[KenReay]

Hi

Problem I think is, you code is not pausing after openning the form to add a new item to the collection, so code following the DoCmd Executes before new item is added, try using windowmode acdialog see modified code below.

But as an Asside, if you just want to add a single column to a simple look up table, why bother with a form, why not just use the NewDate in an SQL append query to add the row to the table?

Private Sub Collection_edit_NotInList(NewData As String, Response As Integer)
'Routine to handle case where user attempts to enter a new "Collection" name
On Error GoTo Err_Collection_edit_NotInList
NewData = Capitalise(DataTrim(NewData))
Response = acDataErrContinue
If MsgBox("'" & NewData & "' is not an existing collection. Would you like to add this collection now?", vbYesNo + vbQuestion + vbDefaultButton1, "Collection not found...&quot = vbNo Then
Me.Collection_edit.Undo
Me.Collection_edit.Dropdown
Exit Sub
Else
DoCmd.OpenForm "Add a new collection", , , , acFormAdd, acDialog
Forms![Add a new collection].[collection_name] = NewData
newcode = Code3letter(NewData, "&quot
Forms![Add a new collection].[collection_ID] = newcode
Me.Collection_edit.Requery
'Forms![Add a new collection].SetFocus
'DoCmd.Close , , acSaveYes
End If

 

[Uaikega]

Thanks for your help.

Unfortunately, AcDialog doesn't do what's needed here - I suspect my problem is, as you say, creating a redundant form in the first place.

Sorry to be so dumb, I am an old Clipper programmer writing his first effort in Access and not finding it at all easy - but what is the correct syntax for the neccessary append?

 

[KenReay]

Hi Fellow old Clipper programmer!

You do not say which version of Access you are using, and if you are using ADO or DAO as the data access mechanism, but assuming DAO then you need something like

Dim Db As Database
Dim StrSQL as String

Set Db = CurrentDb()
StrSQL = "INSERT INTO tblCollect (strColName) VALUES ('" & NewData & "');"
Db.Execute strSQL

Set Db = Nothing

You need to substitute you own column, table names etc, the sytax for the insert statement is:

NSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Regards
Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[danvlas]

Only the 'Else' branch is to be revised:

Else
DoCmd.SetWarnings False
'use KenReay's suggestion
'or open a recordset (first Dim it at the top):
Set rst=CurrentDb.OpenRecordset("Select * From TableName Where 0 = 1;&quot
With rst
.AddNew
!FieldName = NewData
!RequiredField = "Whatever value you choose"
.Update
.Close
End With
Set rst = Nothing
DoCmd.SetWarnings True
' this is to let you get out of the control
[Collection_edit].LimitToList = False
'you can't requery the control until data is saved,
' so don't even try it here, as you won't get past the
'BeforeUpdate event...
End If


Sub Collection_edit_GotFocus()
'make sure that control is limited to list
[Collection_Edit].LimitToList = True
'requery data when stepping into the control
[Collection_Edit].Requery
End Sub

Hope this helps somehow...

Dan

 

[Uaikega]

Thanks a lot for this help folks!

Am working on the implications - will get back to you about how things end up actually working

 

[Uaikega]

Thanks a lot for this help folks!

Am working on the implications - will get back to you about how things end up actually working