PArameter Passing,ect

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Okay, I am going nuts. I am so use to web programming(asp,asp.net,vb.net,
Click to expand...
vbscript) and now I need to develop an application for someone using ACCESS
front end.

Then you need to learn Access :-) It is a big product, you will not learn
it effectively just by folling around. Get a good book from somewhere & work
through it step by step.

Basically I have a few questions:
Click to expand...
1) How to clear(as in remove the items) the *entire* listbox. For
Click to expand...
instance I have tried lst.clear but that is not a valid function. Right now
I am removing them one by one via the removeitem. Is there a way to
completely clear it without looping it myself?

The listbox has a property which defines its source of data. I don't have
Access here to check, so I can not quite remember its name. Call up the
Properties box of the listbox control, & look at the Data tab - the prop in
question will be there. Just erase that property (manually, or through code)
& the listbox data will disappear.

But why do you >want< to erase the listbox? That is not a common
requirement. Ther may be a better way of achieving what you want to
achieve - whatever that is!

2) How to pass a control to a sub procedure. For instance (snip)
Click to expand...

If your contolr (listbox or otherwise) was named ctlBlah:

mysub ctlBlah ' call the sub, passing the control.

then declare the sub as:

sub mysub (pCtl as control)
' now, pCtl is the passed control.
...
end sub

3) I read somewhere that if you develop a mde file that you can not use
Click to expand...
filedialog control that comes from the office reference 10.0 library. Is
there another filedialog control I cna use. I couldn't find one to use in
access library itself.
 
Okay, I am going nuts. I am so use to web programming(asp,asp.net,vb.net, vbscript) and now I need to develop an application for someone using ACCESS front end

Basically I have a few questions

1) How to clear(as in remove the items) the *entire* listbox. For instance I have tried lst.clear but that is not a valid function. Right now I am removing them one by one via the removeitem. Is there a way to completely clear it without looping it myself

2) How to pass a control to a sub procedure. For instance item number one I was trying to make that a sub procedure where you could pass it any listbox and it would go through and remove the items. However, everytime I try to pass the list box control to the subroutine it tells me "Object required" when debugging I look at the code and the Object itself is set to NULL because nothing has been selected in the listbox. Is there a way to pass the *entire control* to a sub procedure? i ahve tried byRef and that doesn't seem to work either. How I am passing it is Me.Listbox and the parameter has been set to either byRef box as control or byRef box as object. I have even tried byval but no dice. I imagine it is becuase it thinks it Null in value. Which it is not. Becuase there are items listed there in the bo

3) I read somewhere that if you develop a mde file that you can not use filedialog control that comes from the office reference 10.0 library. Is there another filedialog control I cna use. I couldn't find one to use in access library itself

Any help would be greatly appreciated with this..

Yours in deepest gratitude

Sacre
 
#1 - Did you try setting the recordsource to Nothing?

#2 - I am not sure you need to pass the whole control.
Here is some sample code:
A Multi-Select ListBox can not be used directly in the Query Grid Criteria.
e.g. Forms![FormName]![ListBoxName] will NOT work.
Why?
Simply examine the output of the Selection and you will see that the string
produced is different from the one the Query Grid requires:
"MyText1 Or MyText2 Or MyText3" from Listbox
"MyText1" Or "MyText2" Or "MyText3" from query grid.

The solution to the multi-select listbox problem is to write the query in
code so that it looks the same way the query grid does.
Build the criteria based on selected values that you can determine by
looping through the selected property of the list box.

I usually write the query in the Grid and use multiple criteria and then
copy the SQL to code and work it into this format:


Private Sub btnCreateInvoice_Click()
On Error GoTo Err_btnCreateInvoice_Click

Dim frm As Form, ctl As Control, varItm As Variant, strCriteria As
String
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
Set frm = Forms![CreateInvoiceData]
Set ctl = frm!ListPOs
strCriteria = ""

For Each varItm In ctl.ItemsSelected
strCriteria = strCriteria & "(pohdr.ponumber)='" &
Trim(ctl.ItemData(varItm)) & "' Or "
Next varItm

If strCriteria = "" Then
MsgBox "Select one or more PO's."
Exit Sub
End If

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT [invnumber], Date() AS invdate, pohdr.vendno "
strSQL = strSQL & "FROM pohdr INNER JOIN poln ON pohdr.pokey =poln.pokey
"
strSQL = strSQL & "WHERE (" & strCriteria & ");"

If QueryExists("Inv1") = True Then
dbs.QueryDefs.Delete "Inv1"
End If

Set qdf = dbs.CreateQueryDef("Inv1")
qdf.SQL = strSQL

Set ctl = Nothing
Set frm = Nothing
Set dbs = Nothing

Exit_btnCreateInvoice_Click:
Exit Sub

Err_btnCreateInvoice_Click:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_btnCreateInvoice_Click

End Sub


Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function


To de-select all items in a list box try:
Dim lngX As Long

With Me![lstMyListBox]
For lngX = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(lngX)) = False
Next lngX
End With

#3 -
For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With
========================================

--
Joe Fallon
Access MVP



Sacred said:
Okay, I am going nuts. I am so use to web programming(asp,asp.net,vb.net,
Click to expand...
vbscript) and now I need to develop an application for someone using ACCESS
front end.
Basically I have a few questions:

1) How to clear(as in remove the items) the *entire* listbox. For
Click to expand...
instance I have tried lst.clear but that is not a valid function. Right now
I am removing them one by one via the removeitem. Is there a way to
completely clear it without looping it myself?
2) How to pass a control to a sub procedure. For instance item number one
Click to expand...
I was trying to make that a sub procedure where you could pass it any
listbox and it would go through and remove the items. However, everytime I
try to pass the list box control to the subroutine it tells me "Object
required" when debugging I look at the code and the Object itself is set to
NULL because nothing has been selected in the listbox. Is there a way to
pass the *entire control* to a sub procedure? i ahve tried byRef and that
doesn't seem to work either. How I am passing it is Me.Listbox and the
parameter has been set to either byRef box as control or byRef box as
object. I have even tried byval but no dice. I imagine it is becuase it
thinks it Null in value. Which it is not. Becuase there are items listed
there in the box
3) I read somewhere that if you develop a mde file that you can not use
Click to expand...
filedialog control that comes from the office reference 10.0 library. Is
there another filedialog control I cna use. I couldn't find one to use in
access library itself.
 
Is there a way to pass the *entire control* to a sub procedure? i ahve
tried byRef and that doesn't seem to work either. How I am passing it
is Me.Listbox
Click to expand...

Nearly right: the dot operator returns a property of the Form object, so
that usually refers to a field in the recordset, but it can be a control if
no field exists with the same name. It is safer to use the bang (!)
operator, which returns a member of the Form object's Default Collection,
which happens to be the Controls collection, which is what you want. It is
safest of all to use the Controls collection explicitly, but rumour has it
that this is slightly slower. This is okay:-

ClearControl Me!lstPickSomething, False

or:-

ClearControl Me.Controls("lstPickSomething"), False


My guess is that the definition of the procedure is not set up right. You
will need to declare the argument as a Control. Although I have not tested
this, if you declare it as Variant (an easy mistake coming from vbs), then
it probably gets the Default Property rather than the object itself.
Therefore you will want,

Public Sub ClearControl( _
SomeControl As Control, _
DisplayWarning As Boolean )

' only process a genuine list box
If SomeControl Is ListBox Then
' user interaction
If DisplayWarning = True Then MsgBox "Clearing..."

' I haven't looked this up, but it's prolly what you want
' You could always try looking up the help file for
' methods of the ListBox object
SomeControl.RowSourceType = "Value List"
SomeControl.RowSource = ""
SomeControl.Requery

Else
' do nothing if it's not a list box

End If

End Sub



Of course, if you want to set it back to something, you'll need to check in
help how to do that.


Hope that helps



Tim F
 
Sacred said:
1) How to clear(as in remove the items) the *entire* listbox.
For instance I have tried lst.clear but that is not a valid
function. Right now I am removing them one by one via
the removeitem. Is there a way to completely clear it
without looping it myself?
Click to expand...

You are way too vague here. What do you mean by "clear"?
Others have told you you can make the list contain no items
to select by:
Me.lstBox.RowSource = ""

But I suspect that you really want to leave the list intact
and only unselect the items that the user selected. If so,
how you do that depends on what you have in the list box's
MultiSelect property. For None, just set the list box's
Value property to "" or Null as appropriate to your needs.
For Simple or Extended, I think your best bet is to reset
the RowSource property:
Me.lstBox.RowSource = Me.lstBox.RowSource
 
Hey Tim Thanks for the different ways

But NONE of them worked as far as passing parameters as a contro

HEre is the simple code

either declaration of sub procedure trie
Public Sub ClearListBox(ByRef box As Control
Public Sub ClearListBox(ByVal box As Control
Dim i As Intege
For i = 0 To box.ListCount -
box.RemoveItem (i
Next
End Su

I have tried these calls you gav

ClearListBox fUserSecurity!blst_aCurrentUser
ClearListBox Me.controls("blst_aCurrentUsers"

It still barfs trying to get into the sub procedure

I have tried this a variety of ways. I just can't beleive Access won't allow you to pass controls to a procedure

However, the second solution you gave about clearing it out instead of looping through and removing each item works like a champ. I didn't realize you could do a requery on a value list wihtout causing an error

I wish I could get it to pass byref controls to a subprocedure.. As there are a couple of different times I am repeating code that could be a simple procedural call

Any other suggestions are greatly appreciated

Sacre
 
Thanks Joe

#1 This is an unbound data control. I am doing all the work in the code behind area. But Tim's solution did work on requery the control. You would think that would cause an error if the recordsource="" and it is a value list but it doesn'

#2 Thanks for your example. I gleened a solution off of it Hopefully. That is to pass the controls name and the form name to the subprocedure then through the database reference. I will try this later and see if it works and let you know

#3 Thanks for the Dialog Example. I think I need to learn about the application object more. Becuase I looked for it and couldn't find the access version of a dialog box. I put it in and it worked bueatifully. So thank you very Much. Now I don't have to worry about the OFfice Library 10.0 reference issue

Thanks Alot for your help

Yours in deepest gratitude

Sacre

I was hoping someone could provide some clarification on that
 
Marsh

Buddy how can I get any more specific did you read what I wrote?

"How to clear(as in remove the items) the *entire* listbox." <-- from my original messag

Clear = remove the item

Your hunch is wrong. I want to clear or remove or how about I want a blank the list bo

I am using unbound data and I want to refresh the list manually by hand. You know the old fashion way of looping through and adding items. As I am using sql server with stored procedures on a SQLOLEDB connection string

Many thanks for your time though

Sacre
 
sacred said:
Buddy how can I get any more specific did you read what I wrote?

"How to clear(as in remove the items) the *entire* listbox." <-- from my original message

Clear = remove the items
Click to expand...

I interpreted that as possibly meaning you wanted to remove
the items that were selected. Sorry if that wasted your
time.

Your hunch is wrong. I want to clear or remove or
how about I want a blank the list box
Click to expand...

I and others have directed you to setting/resetting the
RowSource property as a simple way to do this.

I am using unbound data and I want to refresh the list
manually by hand. You know the old fashion way of
looping through and adding items. As I am using sql
server with stored procedures on a SQLOLEDB
connection string.
Click to expand...

Well, you see that never was the "old fashioned way" in
Access. Until the latest version, there never were the
Add/Remove Item methods. I guess I got confused by your
speaking VB and I was thinking Access. While the two
environments are very different, the objects, properties,
methods and terminology are just similar enough to create
all kinds of confusion.
 
Hey Marsh,

Yeah, I can understand the confusion with it.

My head is spinning just doing asp/VBscript and VB.NEt/ASP.NET. Now comes along this with VBA wanting advanced level coding. Personally I think this thing should have been written in VB with crystal reports but the client asked for Access Front End, with triggers/stored procedures via sql server connection. Unfortunately the client is in Virginia and it is a hospital processing refund checks. I will not be setting this up for them. Rather it will require as much flexibility as possible for them to hook up to the sql server stuff.

The kicker is I am importing a delimited file (which was created from a sql server at their site) into sql server. Then I am exporting a delimited file from sql server, which will be sucked in by a SQL Server on their site. Too Top it off they are circumventing their MIS department and the non-techies are doing the specing.

I also have worked with simple ACCESS database binding data, Linking SQL Server table and queries via odbc, ect.. When I worked for Digital Equipment Corporation. Very easy to do, very cool in certain respects. But it starts getting complicated when you are not working onsite and have to go by what is being asked for.

And Yes I don't know if you read my other response but the rowsource="" and requerying on that works like a champ.

So did the Application.FileDialog.

Now hopefully my solution will work on the creation of generic subs for altering controls...

And no I didn't know that about prior versions of ACCESS.

Thanks much for your reply.

Have a Happy Thanksgiving,

Sacred
 
sacred said:
Hey Marsh,

Yeah, I can understand the confusion with it.

My head is spinning just doing asp/VBscript and VB.NEt/ASP.NET. Now comes along this with VBA wanting advanced level coding. Personally I think this thing should have been written in VB with crystal reports but the client asked for Access Front End, with triggers/stored procedures via sql server connection. Unfortunately the client is in Virginia and it is a hospital processing refund checks. I will not be setting this up for them. Rather it will require as much flexibility as possible for them to hook up to the sql server stuff.

The kicker is I am importing a delimited file (which was created from a sql server at their site) into sql server. Then I am exporting a delimited file from sql server, which will be sucked in by a SQL Server on their site. Too Top it off they are circumventing their MIS department and the non-techies are doing the specing.

I also have worked with simple ACCESS database binding data, Linking SQL Server table and queries via odbc, ect.. When I worked for Digital Equipment Corporation. Very easy to do, very cool in certain respects. But it starts getting complicated when you are not working onsite and have to go by what is being asked for.

And Yes I don't know if you read my other response but the rowsource="" and requerying on that works like a champ.

So did the Application.FileDialog.

Now hopefully my solution will work on the creation of generic subs for altering controls...

And no I didn't know that about prior versions of ACCESS.

Thanks much for your reply.

Have a Happy Thanksgiving,

Sacred
Click to expand...


Non-techs creating specs sounds like another oportunity for
all kinds of confusion :-(

It does sound like an interesting project in some respects
though. Good luck with it and don't try to deep fry the
turkey ;-)
 
It still barfs trying to get into the sub procedure.
Click to expand...

More info please: what error are you getting _exactly_? And which line is
highlighted in the editor if you choose the 'Debug' button?
box.RemoveItem (i)
Click to expand...

You have not shared which version of Access you are using, but up to 2000
at least, there is no RemoveItem method -- have you checked the help files
or Object browser for information on the Listbox control? Many of the
controls in Access are completely different from their cousins in MS Forms
and VB.
I have tried this a variety of ways. I just can't beleive Access
won't allow you to pass controls to a procedure.
Click to expand...
That's because it is not true. Access does pass all kinds of objects into
and out of subroutines quite happily. Since you haven't told us what errors
you are getting, though, it's not easy to guess what is going wrong for
you.

All the best


Tim F
 
Hi Tim

I tried it again and figured it out. I am "old" school programming started with C,C++ (12 years in programming). I am so use to using parathensis from the college days

That when I was making the call to the sub procedure I was doing parenthesis around the parameters. SO it thought it was an array

Sorry about that. I guess that is what happens when you cross languages. You start doing 1/4 C,c++, 1/4 VB, 1/2 VB

That error has caught me more than once while developing this application I am writing. I am use to .NET environment catching that mistake

Thanks much for your time

Have a safe and happy thanksgiving

Sacred
 
I tried it again and figured it out. I am "old" school programming
started with C,C++ (12 years in programming). I am so use to using
parathensis from the college days.
Neophyte!

That when I was making the call to the sub procedure I was doing
parenthesis around the parameters. SO it thought it was an array.
Click to expand...
Aha: when you put a parameter around an expression, it gets evaluated:
therefore DoSomething (Something) doesn't send Something but the result of
calculating Something -- which presumably means the default property
(.Value usually). Because C is so much more strict about pointers and data
types than Basic, you would not notice.

You can call a function:-

AVariable = GetSomething( AValue )

or a procedure

DoSomething AValue

or this one, for old times sake

Call DoSomething( AValue )

It's second nature after a while -- which is prolly why I never even
thought to ask specifically.

All the best


Tim F
 
TC said:
Or, just to get really excited:

Call DoSomething ( ( AValue ) ) !
Click to expand...

Oh please don't confuse him any further... The point is that this is not
functionally equivalent to

DoSomething AValue

because it will evaluate the variable first, which is what the OP...
[continued on page 1]



All the best


Tim F
 
Back
Top