"DINAMIC" command buttons

[IT-1957]

Hello every one,

I'm creating a little POS, I'm at the point where the cashier has the option
to look up a product, What I did is that the user clicks a button {look up
product} a form opens, what I need to do at this point is that all the
products on a table (or query) like "fruits" will appear on this form, but in
a command button each (with the option to add a picture to the button) this
is intented for a touch screen, so the user can quickly select the product.
I can create the command buttons and all the actions for them to do what i
want, but it is possible to have the "dinamic" buttons?
That is that if I add a new product on the "fruits" table a new command
button for that record will be created on the form as well....

Or something similar...

Any idea on how to accomplish this?

Thank you for your help.

 

[Tom van Stiphout]

On Fri, 5 Dec 2008 19:57:01 -0800, IT-1957

It's not uncommon to at design time create a form with a full set of
buttons. Then look in the database, and put captions on them. If you
have fewer than the full set of buttons, hide the other ones.

One thought is to name these buttons with some naming convention, e.g.
Button0 ... Button24, so you can write:
set rs = db.Openrecordset(...)
for i = 0 to 24
Me.Controls("Button" & i).Caption = rs!Caption
rs.MoveNext
next i

-Tom.
Microsoft Access MVP

 

[IT-1957]

It is eaxactly what I try to do, but I'm farly new with the execution VB,
The code you wrote, will go in the On open event of the form or where?
I started by creating 20 command buttons, command1,command2 etc,
I have a query with the 20 most used products, can i name command1 as the
first product on this query?
Or going back to your code:

set rs = db.Openrecordset(...)

for i = 0 to 24
Me.Controls("Button" & i).Caption = rs!Caption
rs.MoveNext
next i

What should I put on the (...)
What does i means?

Thank you for your help, I'm just trying to understand the code itself so i
can use the concept in the future for other pourposes.

 

[Tom van Stiphout]

On Sat, 6 Dec 2008 08:31:01 -0800, IT-1957

Here is a more complete example:
Dim rs As DAO.Recordset 'Requires reference to DAO 3.6
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("myQuery", dbOpenSnapshot,
dbForwardOnly)
i = 0
While Not rs.EOF
Me.Controls("Button" & i).Caption = rs!Caption
i = i + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

I would put this code in the Form_Load event.
In the above example your top-20 query is called "myQuery", and your
buttons are named "Button0" through "Button19".
i is just a variable we can use in a for loop as before or increment
it ourselves as in this new example, and it gets appended to the
button name "Button" & i to create the button names Button0 etc. I
could have named it anything I want.

-Tom.
Microsoft Access MVP

 

[IT-1957]

Tom, I just tried,

I created the query and named it "myquery" --this query has two columns "UPC" and "DESCRIPTION" showing 20 records
(I know I could have named it anything I wanted, but just to test it)
I created the form with 20 command buttons and named them Button0---19
I copied the code and pasted id on the load event of the form.

I get a sintax error on the line
Set rs = CurrentDb.OpenRecordset("myquery", dbOpenSnapshot,
dbForwardOnly)

???

 

[Tom van Stiphout]

On Sat, 6 Dec 2008 09:29:00 -0800, IT-1957

Maybe because you didn't follow my instructions in the comment right
after that line. Code Window > Tools > References > check Microsoft
DAO 3.6

-Tom.
Microsoft Access MVP

 

[IT-1957]

Tom,

It is checked: "Microsoft DAO 3.6 Object Library"

The error my be happening on the load event...when I get the debugger a
yellow arrow and highlited line appear on the first line of code: "Private
Sub Form_Load()"

I really apreciate yur help.

Private Sub Form_Load()
Dim rs As DAO.Recordset 'Requires reference to DAO 3.6
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("myquery", dbOpenSnapshot,
dbForwardOnly)
i = 0
While Not rs.EOF
Me.Controls("Button" & i).Caption = rs!Caption
i = i + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub

 

[Stuart McCall]

Tom, I just tried,

I get a sintax error on the line
Set rs = CurrentDb.OpenRecordset("myquery", dbOpenSnapshot,
dbForwardOnly)

<snip>

PMFJI

Lose the dbForwardOnly argument. From the help file:

"Creates a forward-only Recordset (Microsoft Jet snapshot-type Recordset
only). It is provided only for backward compatibility, and you should use
the dbOpenForwardOnly constant in the type argument instead of using this
option."

So your call would look like:

Set rs = CurrentDb.OpenRecordset("myquery", dbOpenSnapshot)

HTH

 

[IT-1957]

That solved the sintax error on that line of code, now I get the error on
this line:

Me.Controls("Button" & i).Caption = rs!Caption

It says: Run time error: 3265
Item not found on this collection

 

[Beetle]

If your buttons are actually named command1, command2, etc. like you
indicated earlier, then you need to use;

Me.Controls("command" & i).Caption = rs!Caption

Tom's example was based on buttons named Button1, Button2, etc.

 

[IT-1957]

I did name them Button0,Button1....till Button19

IT1957
----

 

[Marshall Barton]

I did name them Button0,Button1....till Button19

IT1957

How many caption records are in the recordset?

 

[IT-1957]

If you mean on "myquery" I have 20 records.
Do I have to bound the form to "myquery" as data source? (i did but still
the same problem)

 

[Marshall Barton]

If you mean on "myquery" I have 20 records.
Do I have to bound the form to "myquery" as data source? (i did but still
the same problem)

If there are 20 records, then the code you posted requires
that there be 20 buttons named Button0, Button1, ...,
Button19

 

[Tom van Stiphout]

On Sat, 6 Dec 2008 11:54:01 -0800, IT-1957

You mentioned earlier that your query returned "UPC" and
"DESCRIPTION". So it is not returning rs!Caption. Consider adjusting
that expression.

-Tom.
Microsoft Access MVP

 

[IT-1957]

Could you help me adjusting it?
I'm new with the VB code...

 

[Marshall Barton]

Could you help me adjusting it?
I'm new with the VB code...

Have you created Button0 yet?

 

[IT-1957]

Thank you all for your help, John W. Vinson [MVP] helped me fix the code:
Instead of rs!Caption use whatever the actual fieldname is: rs!UPC
It works like a charm.
There was another similar question back since september, that i founded
after i posted this question, at the end, Jhon replayed to it and helped me
fix the code.

Thank you all.