Temporary Storage for Combo Box is wrong! 1

[Petemush]

Hi,

There's a couple of threads around concerning storage of user entered values in combo boxes but none seem to correspond to my new problem.

I've managed to find and amend some code so that the user is prompted if they want to add their value to the combo box. The code is thus:


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

Dim intMsgBox As Integer
Dim strData, quotes As String
quotes = Chr(34)

strData = Trim$(NewData)
intMsgBox = MsgBox("""" + strData + """" + " does not appear in the database. Do you want to add it now?", vbQuestion + vbYesNo)
If intMsgBox = vbYes Then
[Course Type].RowSource = [Course Type].RowSource & ";" & quotes & strData & quotes
Response = acDataErrAdded
End If
End Sub


The problem is, it's not permanent. Once the form has exited, the addition is lost.

I think maybe the problem is I need to change the row source of the field [Course Type]in the Table tblCourses which is what the form frmCourses is based on but I can't remember how to refer to it!

If that's not it (which it probably isn't) does anyone know what it is?

Cheers,

Pete

 

[LittleSmudge]

You are well onb the way to the answer Pete.

The
[Course Type].RowSource = [Course Type].RowSource & ";" & quotes & strData & quotes

works fine if the original RowSource for the combo was a value list.

HOWEVER, if ( as I think you're hinting at ) the RowSource comes from a table ( via a query ) then your solution will only ever be temporary.

You need to write the data to a new record in the table that holds the data for the RowSource.
To do that you need to open a Recordset and perform a RecordSet.AddNew

The code for this is not diffecult but does depend on whether you're using [up to A97] DAO or [A2k & beyond] ADO

If the recordset code is a challenge let us know what version you are using and the table name and field name that hold the data at the mement as well as the current value of the RowSource parameter.


'ope-that-'elps.

G LS

 

[Petemush]

Cheers LittleSmudge,

Unfortunately it IS based on a value list yet it still persists in not being permanent.

What i thought was, since the control is bound to a field in tblCourses, that I would need to change the RowSource property of the field in tblCourses as well as the RowSource property for the control on the form frmCourses.

I tried the follwing additions to the above code

Dim rs As Recordset
Dim ct As ComboBox

Set rs = OpenDatabase("Training.mdb&quot![tblCourses]
Set ct = rs![Course Type]

Then in the if statement once the Control's RowSource property is changed:

ct.RowSource = [Course Type].RowSource

This doesn't work since I get a type mismatch error from the Set rs = OpenDatabase..... line.

Any ideas?

 

[LittleSmudge]

What i thought was, since the control is bound to a field in tblCourses, that I would need to change the RowSource property of the field in tblCourses as well as the RowSource property for the control on the form frmCourses.

Arrrh ! You've got those dreaded Lookup Fields haven't you !!!

See the thread:-
thread700-295181 tblCourses in the Current Database ?
Are you using A97 - or earlier ( or are you on A2k, A2k2 ) ?


Yes, you're right
Set rs = OpenDatabase("Training.mdb&quot![tblCourses]
will not work.
I'll need answers to the two questions above to sort out what it should be.

What actually appears in the RowSourceType and RowSource fields in the combo box ?


When you close the form is the automatic close without saving changes set ?
eg using :-
DoCmd.Close , , acSaveNo



G LS

 

[Petemush]

LS,

I'm using A97 but only because the company I'm working or are taking FOREVER to upgrade to 2000. The final product will be run on 2K which means the further I go into doing this database, the more problems I'm going to have when I move it to 2K. Can't wait for that!

If I understand your second question, then yes tblCourses is in the Current Database.

The Rowsource Type for the combobox contains Value List and the RowSource for the cpmbo contains

"Administration Skills";"Appraisal Skills";"Communication Skills";"Core Management";"Finance";"Health & Safety";"Inclusion";"Induction";"IT Training";"Management Development";"NVQ";"Personal Development";"Recruitment Skills"

The bound column is set to 1.

Don't know about the onclose,haven't set anything myself but everytime I've tested it I've made sure to click save before I closed it.

If I should just abandon Look Up Fields and there's a better way then please advise!

Cheers,

Pete

 

[LittleSmudge]

For general expandability of the database in general you should put all of the combo box entries into a table.

Then your On_Not_In_List event simply adds another record to the table.

OR you prevent users from adding anything to the list - but provide the system administrator with the facility to add records to the table.


As for A97 to A2k well you are going to have fun aren't you !

When you port to A2k you can bring in the DAO 3.6 reference and all of your DAO code should work. That will be the 'quick way' - rather than converting all of your DAO code to ADO.


G LS

 

[Petemush]

Thanks for all your help LittleSmudge, I'm going to put the whole Lookup fields in the bin and follow your advice and create tables holding the combo box values.

All the relevant computers that will run this database have been updated to A2K except my one! I've got A2K at home, thinking about taking it home and converting it there every night to deal with issues when and where they arise. Expect I'll have a lot ADO/DAO questions in the near future!

Thanks again,

Pete