two questions. 1) rowsource 2) refresh combo box list after add a record

[lulu123]

here's my problem

1) rowsource

.RowSource = "select distinct [projectID],[projectDescription] " & "from Projects " & " where [projectID]=" & Me![projectID]

I have the above code, and it works fine. however, i want to make the combo box in alphabetical order by [projectDescription]. I tried to add "order by [project Description] " after "me![projectID]". But it gives me error msg. Can someone tell me how can i do that. Thanks.

2) REfresh the combo box list after add a record.

i have a tab in the main form called "add a projectDescription", which opens a new from and allow the user to add a new record to the " projectDescrition' combo box list. What my problem is that, the added record won't show in the combo box usless you close the form and reopen it again. Is there anyway i can refresh the combo box list without have to close the form.

Thanks.

[cori25]

Hello...

1 - When you are looking at the row source, to the right is a box with 3 dots(...), click on this and you will now be in the query inside your form. Go to the field "Project Description" and sort ascending. *NOTE* Do not hit save, once you have made this change just close out the query and it will prompt you if you would like to save the changes, hit yes and you will be all set.

2 - I assume you have a command button to add the record, the best way to make this process easier for the end user is to create a macro that will close the form and then re-open the form when they click the "submit" button. Just go to the properties of the button and scroll down until you see On Click, then select the macro you just created.

Let me know if this helps.

Thanks

[lulu123]

cori, thanks for reply, but this doesn't really solve my problem.

1), my rowsource is part of the a vba code, where there was if condition as well. So, i can't really use the property box with the "..." to make it in oder. if i am writing this in VBA code, Do you have any other suggestions?

2), for the 2nd problem, i dont' really want to close and reopen the form. Because there is a filter on the form, where different user choose their own name first and then it took the user to the main form to enter their own data. If i do what you suggested, will it just open the form with everyone's record and not to the exact user?

Please give me more suggestion. Thanks.

[cori25]

I avoid using VBA when dealing with forms since it is much easier to just build behind the form. Therefore, no I do not know of any other way to have the data pull in ascending order because when I use vba I either pull the data from a query or a table and make sure it is ascending there.

As for refreshing the way I had mentioned, it will not open the form to everyone's information. It will open the form and each combo box will be blank, then the user will have to go through and pick there info from the drop down box and hit submit.

I have researched this in the past and this is the easiest approach. I usually include an exit button so that once they have completed adding information, the database will save all and close. Unless I am misunderstanding what you are asking, this will be the best approach.

Thanks

[lulu123]

Thanks for reply cori.

for the first problem, i find the solution. which the code as following

.RowSource = "select distinct [projectID],[projectDescription] " & "from Projects " & " where [projectID]=" & Me![projectID] & " order by projectDescription"

I avoid using VBA when dealing with forms since it is much easier to just build behind the form. Therefore, no I do not know of any other way to have the data pull in ascending order because when I use vba I either pull the data from a query or a table and make sure it is ascending there.

As for refreshing the way I had mentioned, it will not open the form to everyone's information. It will open the form and each combo box will be blank, then the user will have to go through and pick there info from the drop down box and hit submit.

I have researched this in the past and this is the easiest approach. I usually include an exit button so that once they have completed adding information, the database will save all and close. Unless I am misunderstanding what you are asking, this will be the best approach.

Thanks