Fill combo box from new record automatically (1 Viewer)

[acarter1]

Dear all,
Thank you for all your support with the databases in the past!
I have now come across one small problem in my database:

I have a form with a subform, the form is the persons details and the subform is their leave record eg. date of leave to be taken, days off, name of course, reason for attendance etc. Next to the combo box to select the name of the course to attend there is a button which opens a new form in add mode to add a new course, the details of the course are ID(autonumber), Name, Location. My question is how when the course is added and the close button is clicked to return to the leave form can i make the combo box on the leave form automatically pick up the course i just added to the system so i do not have to scroll through 200 courses each time and select the course??

Any Help Would be greatly appreciated!
Regards,
Alex

 

[DCrake]

First of all you need to get the combo box to repopulate its contents so that the new item appears in the list.

Secondly you will need a loop to loop through all the items in the combo until it locates the new item then you use the .Selected = True to hightlight the new item.

Where n is the index number in the list.

Eg:

For x = 0 to Me.combo.Listcount -1
If Me.Combo(x,1) = "New Text" then
  Me.Combo.Selected(n) = True
  Exit For
End If
Next

X = Index number
1 = Column number (zero based)

The above aircode is untested

 

[acarter1]

Alright that looks great! i'll have a try in work tomorrow.
Just a couple of questions,
Would i have to declare x and n as variables and if so what data type?
And finally would this work on the close button of the course add form to reference to the subform on the main form.

Sounds a bit hard so heres the form structure:

frmLeave - Main form contains employee main details.
frmLeaveSub - Subform from frmLeave with details of the leave entry and on this each record has a combo box and a add new course button to open frmAddnewcourse.
frmAddNewCourse - this is a form in add mode that asks for confirmation of saving new details in text boxes "ID" "name" "venue" on the click of a close button on the form. this is the form i would like this event to occur on when closed.

Thanks!

 

[pkstormy]

Anther way is to store the 'RecordID' (probably the autonumber field) to some variable and then do a docmd.findrecord.

ex (on the subform):
SomeVariable = me!RecordID (somevariable is declared under option explicit for the form)
....
....do something....

then code to find existing record
....
me.MyRecordIDField.setfocus
docmd.findrecord Somevariable
me.SomeOtherfield.setfocus (since you'll most likely want to lock the RecordID field. - but keep it enabled and visible.)

or if going to a new record and you then want to set the RecordID to a value.
Somevariable = me!MylinkingFieldName
docmd.gotorecord,,acnew
me!MyLinkingFieldName = Somevariable

(although you could also easily set the default value as well)
me.MyLinkingFieldName.defaultvalue = "=" & me!MyLinkingFieldName
docmd.gotorecord,,acnew

 

[acarter1]

Ok so this is the code for the close button on the form:

Private Sub close_Click()
For x = 0 To Form_subfrmLeave.CourseID.ListCount - 1
If Form_subfrmLeave.CourseID(x, 1) = Me.CourseID Then
Form_subfrmLeave.CourseID.ListIndex
End If
Next
On Error GoTo Err_close_Click

DoCmd.close
Exit_close_Click:
Exit Sub
Err_close_Click:
MsgBox Err.Description
Resume Exit_close_Click
End Sub

I am getting an error saying invalid use of property: meaning the listindex but i don't know what is wrong!
Someone please help!