[URL="http://forums.devshed.com/member.php?u=32194"]Shad0w[/URL] [IMG]http://images.devshed.com/fds/statusicon/user_offline.gif[/IMG]
Junior Member
[LEFT][IMG]http://images.devshed.com/fds/belts/ds_forums.gif[/IMG][/LEFT]
Join Date: Jul 2003
Posts: 1 [IMG]http://images.devshed.com/fds/reputation/reputation_green.gif[/IMG]
Time spent in forums: < 1 sec
Reputation Power: 0
[B]saving a list off[/B]
First post so i'll try and give an answer.
This post was actually written on another message board by me 1 day before. I asked a similar question but ended up solving it myself. This may slightly differ from what you want but you should be able to use parts of it.
I needed to make a hybrid sql/value list combination to add to a combo box of which the select result from the combo box would filter a subform table.
anyways here we go sorry if some of it doesn't seem to flow as below here was posted on another forum.
------------------------------------
For anyone that may need it here is the answer. While i won't back it up as being the most efficient answer as i'm sure there may be a better way in existance. I'm pretty new to vb and it bamboozles me with all the stuff you don't have to create thats there to use especially with access integration.
However i feel kinda lame answering my own question but here it is.
i'll assume you have a table name ContractDetails with field ContractID and that your
combo box is called cboContractNumber. I'll also assume that the form with the combo box on it is called "Form_frmYourForm" and your using the dao record set rather then the ado (active data object) record sets since i haven't learnt to use it yet but i'm told dao is old school and ado is better. Anyways on with the show.
Please note however that i haven't added any validation error checking but the only part that needs it is you MUST check to see if the table query returns any rows before calling the MoveLast command. If there are no rows returned then you can't really move to the last record of a null set i guess :/ and you'll get a crash and burn situation.
I actually placed this code in the open event of the form but it could be used anywhere.
CODE
' variants can take string, integer whatever data we'll need one of these for
' the database field retrieval. I could use a string since i know the contractID is one
' but better to get into the habit of using variant for later more robust/reusable code
Dim strValueList As Variant
Dim strListQuery As String
Dim strFinalStringList As String
Dim wks As Workspace
Dim db As Database
Dim recContractDetails As DAO.Recordset
Dim iTotalrows As Integer
Dim iCounter As Integer
' set the query we are going to run
strListQuery = "SELECT [tblContractDetails].[ContractID] FROM tblContractDetails;"
Set wks = DBEngine.Workspaces(0)
Set db = wks.Databases(0)
Set recContractDetails = db.OpenRecordset(strListQuery, dbOpenSnapshot)
recContractDetails.MoveLast
iTotalrows = recContractDetails.RecordCount
' grab all the rows. Don't worry the variant will be automatically resized to a
' multi dimensional array
strValueList = db.OpenRecordset(strListQuery, dbOpenSnapshot).GetRows(iTotalrows)
iCounter = 0
' our first value of the field list is set to "All"
finalstring = "All;"
Do While iCounter < iTotalrows ' Inner loop.
' append to it all the extra values
finalstring = finalstring + strValueList(0, iCounter) + ";"
iCounter = iCounter + 1 ' Increment Counter.
Loop
Form_frmYourForm.cboContractNumber.RowSource = finalstring
Also note that you must add some extra code in the cboContractNumber onChange event. You'll have to go something like
CODE
if(chosen item from box == "All")
{
//then filter the table you have displayed with this sql command
docmd.runsql(select * from whatever_table)
}
// its one of the list items from the table so use the normal sql query
else
{
docmd.runsql(select * from whatever_table where contractID = cboContractNumber.value
}
The last bit was semi pseudo/vb/c++ code so if you've gotten the above to work you should be able to fix that up to work.
Anyways it took me some microsoft assistant help searching time and lots of bits and pieces on scrap paper to finally get it to work the proper way.
Anyone with a better/cleaner/more efficient solution please don't hesitate to post it. Else enjoy the code... maybe it can help someone else.
thanks for your time
------------------------------------
If you wish to see the full posting then you can at the following link
Click Here For The Link
I'm not advertising or anything so please don't get mad i merly thought the link may direct others that want to read the full thing to get the idea of what i did and was trying to do.
But again i don't think its the most efficient way so if people can tidy the code up make ti smaller and better then by all means [IMG]http://images.devshed.com/fds/smilies/smile.gif[/IMG] as i'm not the best vb coder out there considering i've used it for around 2-4 weeks.
Anyways i hope it helps.
[I] Last edited by Shad0w : July 16th, 2003 at 05:40 AM.
[/I]
