Cascading combo box sql backend

[ayh1]

I have a cascading combo box that successuflyl returns data from sql server tables venue and date, when I save the record it saves the values but when I re open the record for a particular patient the combo box values are cleared and do not display patients appointment venue and date although it is saved in the database. How can I return the values in the combo boxes which were saved

 

[CJ_London]

How can I return the values in the combo boxes which were saved

check they have been saved with all relevant fields completed correctly first

 

[ayh1]

check they have been saved with all relevant fields completed correctly first

when I save the record the details have been sucessfully passed to the sql server, I have created a text box and d lookup on the same form and it returns the saved data, but the cascading combo boxes clear is it because there is a row source on only one of them?

 

[CJ_London]

OK so all the data is there, but just not showing in the combobox

What is the rowsource to your combobox?

 

[ayh1]

OK so all the data is there, but just not showing in the combobox

What is the rowsource to your combobox?

for venue combo the row source is

SELECT dbo.tblLkup_Location.* FROM dbo.tblLkup_Location

this cascades to combo for date and is populated with venue after update event

  Me.cboDate.RowSource = "SELECT dbo.tblCourses.Course_ID, dbo.tblCourses.CourseDate FROM dbo.tblCourses " & _
     " WHERE Location_ID = " & Nz(Me.cboVenue) & _
     " ORDER BY CourseDate"

 

[CJ_London]

What happens when you click on the combobox 'down arrow' to display the choices?

And assuming there are choices displayed, what happens when you select one of them?

 

[ayh1]

What happens when you click on the combobox 'down arrow' to display the choices?

And assuming there are choices displayed, what happens when you select one of them?

they work correctly and are populated with the correct data

 

[spikepl]

You need to requery the combo boxes in the current event, so their contents fit with what has been recorded.

Errrr update: reset the rowsource for the second combo. WHy dont you let it refer to the first combo as in Forms!myform!MyFirstComboName then you'd just need to requery it.

 

[CJ_London]

they work correctly and are populated with the correct data

OK so what is the control source to the combobox? If blank, it will not default to the previously selected value which is what I think you want it to do - you will need some code to maintain the previously selected value which I can suggest if my understanding is correct

 

[ayh1]

OK so what is the control source to the combobox? If blank, it will not default to the previously selected value which is what I think you want it to do - you will need some code to maintain the previously selected value which I can suggest if my understanding is correct

control source for venue is blank and date is set to CourseID

 

[CJ_London]

control source for venue is blank

This is why it is not 'retained'. Every time you refresh the form with a new patient it will return to blank.

If you want to retain the value you'll need some code which will be like this

In the form module create a public variable

Public VenueChoice as Long


then in your form current event put

if not isnull(VenueChoice) then cboVenue=VenueChoice

and in your cboVenue after update event put

VenueChoice=cboVenue

 

[ayh1]

This is why it is not 'retained'. Every time you refresh the form with a new patient it will return to blank.

If you want to retain the value you'll need some code which will be like this

In the form module create a public variable

Public VenueChoice as Long


then in your form current event put

if not isnull(VenueChoice) then cboVenue=VenueChoice

and in your cboVenue after update event put

VenueChoice=cboVenue

I wll try it out thanks what do you mean by form module? build event?