Two questions for the price of one

[Boumxyz2]

In VB access, I'm filling a combo box with a function instead of a query. I can't put a default value because the combo box is actually filled after the LOAD event of the form. Is there any way I can put a default value to my combo box ? If yes let me know.

Secondly, I would like to know if there's any way to get the application path in VB access ? The curdir function just give me c:\My document which I really don't care about. I would like to know from Where the MDB file has been opened so I can save an excel report there. Any idea ?

Thanks in advance

Nic

[GeorgeT]

You can use the Path property to determine the location where data is stored for a Microsoft Access project (.adp) or Microsoft Access database (.mdb).

Path = application.CurrentProject.Path

[Boumxyz2]

That property or methods doesn't Exists

Application.Currentproject

[GeorgeT]

It does in Access 2000, what are you using?

[Boumxyz2]

Access 97

[robby bassic]

Are you filling the Combo with RowSource or DefaultValue?
If you are using RowSource, then you can still put a value or function in the DefaultValue.

[Boumxyz2]

I'm using a function to fill it.. It's not rowsource. Here's the function

public Function FillCombobox(ctlCurControl as Control, vntID, vntRow, vntCol, vntCode)
on error GoTo DBerror

static aMyArray() as string, intArrayItems as Integer
Dim vntReturnVal as Variant
Dim dbs as Database, rst as Recordset, strSQL as string

Dim counter as Integer
vntReturnVal = null

Select Case vntCode

Case acLBInitialize
ReDim aMyArray(0)
intArrayItems = 0
set dbs = CurrentDb()
strSQL = SQLDate
set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveLast ' to make sure all records are accessed and RecordCount returns
' The right value
rst.MoveFirst
for counter = 1 to rst.RecordCount
aMyArray(intArrayItems) = rst.Fields("ReportDate")
intArrayItems = intArrayItems + 1
rst.MoveNext
ReDim Preserve aMyArray(intArrayItems)
next
rst.MoveLast
If Not rst.Fields("ReportDate") Like Format(Now(), DateFormat) then
aMyArray(intArrayItems) = Format(Now(), DateFormat)
intArrayItems = intArrayItems + 1
ReDim Preserve aMyArray(intArrayItems)
End If
rst.Close
dbs.Close
vntReturnVal = intArrayItems
Case acLBOpen
vntReturnVal = Timer
Case acLBGetRowCount
vntReturnVal = intArrayItems
Case acLBGetColumnCount
vntReturnVal = -1
Case acLBGetColumnWidth
vntReturnVal = -1
Case acLBGetValue
vntReturnVal = aMyArray(vntRow)
Case acLBEnd
Erase aMyArray
End Select
FillCombobox = vntReturnVal

Exit Function

DBerror:
intArrayItems = 1
ReDim Preserve aMyArray(0)
aMyArray(0) = Format(Now(), DateFormat)
vntReturnVal = intArrayItems
FillCombobox = vntReturnVal

End Function




It works pretty well but I can't set a default value which would be the last element of the list.

[robby bassic]

Question, which command or event is calling your FillCombobox() function? (Form Load ?)
Anyways, first you will have to use a Property Let/Get.
In the Property you will assign the value of your last item. Here is an example...

'Place this at the end of your Function (before "Exit Function")


pCtlValue = strTheLastItemInYourListAsString

'Place the following in a Main Mod

Private strValue As String 'This line at top of page (Main Mod)

'The rest, anywhere in Main Mod

Public Property Let pCtlValue(ByVal X As String)
strValue = X
End Property

Public Property Get pCtlValue() As String
pCtlValue = strValue
End Property

Public Function fnDefaultForCtl() As Long
fnDefaultForCtl = pCtlValue
End Function



In the Form, set the Default Value of the control to .... =fnDefaultForCtl()