I have created a form which has 5 fields of a Table as textboxes. The
table has 10 fields. I have added buttons for adding a new record,
saving it and browsing to next, previous record, first record and last
record in table.

For the onclick event of button new record, I added below code

Dim dbMyDB As DAO.Database
Dim myRecordSet As DAO.Recordset

Set dbMyDB = DBEngine.Workspaces(0).OpenDatabase("C:\mydatabase.accdb")
Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset)

myRecordSet.AddNew


myRecordSet.Close
dbMyDB.Close

which is incomplete.

1. What more do i need to add so that when it is clicked the user gets
five empty text boxes to which he can enter data which will add a new
record to the table(after the update button is clicked).

2. Similarly, for the buttons update, move to next, previous record,
first record, last, record, saving the added record is there a site/link
to tutorial which explains how to do these basic tasks for Access 2007.

Thanks

g wrote:
> I have created a form which has 5 fields of a Table as textboxes. The
> table has 10 fields. I have added buttons for adding a new record,
> saving it and browsing to next, previous record, first record and last
> record in table.
>
> For the onclick event of button new record, I added below code
>
> Dim dbMyDB As DAO.Database
> Dim myRecordSet As DAO.Recordset
>
> Set dbMyDB = DBEngine.Workspaces(0).OpenDatabase("C:\mydatabase.accdb")
> Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset)
>
> myRecordSet.AddNew
>
>
> myRecordSet.Close
> dbMyDB.Close
>
> which is incomplete.
>
> 1. What more do i need to add so that when it is clicked the user gets
> five empty text boxes to which he can enter data which will add a new
> record to the table(after the update button is clicked).
>
> 2. Similarly, for the buttons update, move to next, previous record,
> first record, last, record, saving the added record is there a site/link
> to tutorial which explains how to do these basic tasks for Access 2007.
>
> Thanks

Is your form bound to a table/query or unbound?

If bound, why not use navigation buttons? Ex: use the New Form wizard
to help you create a data entry form to get you started.

If bound, you could use a command similar to
    DoCmd.GoToRecord , , acNewRec
for code behind a command button to add a new record. Look at
GoToRecord for further options.

You might want to check to see if the record has been modified.
    If Me.Dirty then...

You might want to see if the record is new or old.
    If Me.NewRecord then...

If you want to move to a particular field, in this case field Test
    Me.Test.Setfocus

I don't know about tutorials. I'd recommend a look at Google or some
other search engine.

On Thu, 24 Jun 2010 13:16:10 -0400, g <g_1@g.com> wrote:

>I have created a form which has 5 fields of a Table as textboxes. The
>table has 10 fields. I have added buttons for adding a new record,
>saving it and browsing to next, previous record, first record and last
>record in table.

It really sounds like you're writing a lot of code and going to a whole lot of
work to do something that a simple bound form does for you all by itself. Why?
Have you used the builtin tools (a continous Form with navigation buttons
turned on, as they will be by default) and intentionally rejected them? If so
why?
--

             John W. Vinson [MVP]

On 6/24/2010 2:11 PM, Salad wrote:
> g wrote:
>> I have created a form which has 5 fields of a Table as textboxes. The
>> table has 10 fields. I have added buttons for adding a new record,
>> saving it and browsing to next, previous record, first record and last
>> record in table.
>>
>> For the onclick event of button new record, I added below code
>>
>> Dim dbMyDB As DAO.Database
>> Dim myRecordSet As DAO.Recordset
>>
>> Set dbMyDB = DBEngine.Workspaces(0).OpenDatabase("C:\mydatabase.accdb")
>> Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset)
>>
>> myRecordSet.AddNew
>>
>>
>> myRecordSet.Close
>> dbMyDB.Close
>>
>> which is incomplete.
>>
>> 1. What more do i need to add so that when it is clicked the user gets
>> five empty text boxes to which he can enter data which will add a new
>> record to the table(after the update button is clicked).
>>
>> 2. Similarly, for the buttons update, move to next, previous record,
>> first record, last, record, saving the added record is there a
>> site/link to tutorial which explains how to do these basic tasks for
>> Access 2007.
>>
>> Thanks
>
> Is your form bound to a table/query or unbound?

It is bound to a table.

> If bound, why not use navigation buttons? Ex: use the New Form wizard to
> help you create a data entry form to get you started.

I see the button wizard now activated after i drag and drop a button.
The Use Control Wizards(with a wand icon) was turned off. Once i turned
it on, did drag/drop for a button it worked fine.


> If bound, you could use a command similar to
> DoCmd.GoToRecord , , acNewRec
> for code behind a command button to add a new record. Look at GoToRecord
> for further options.
>
> You might want to check to see if the record has been modified.
> If Me.Dirty then...
>
> You might want to see if the record is new or old.
> If Me.NewRecord then...
>
> If you want to move to a particular field, in this case field Test
> Me.Test.Setfocus
>
> I don't know about tutorials. I'd recommend a look at Google or some
> other search engine.

Thanks for the reply and advice.

On 6/24/2010 2:27 PM, John W. Vinson wrote:
> On Thu, 24 Jun 2010 13:16:10 -0400, g<g_1@g.com>  wrote:
>
>> I have created a form which has 5 fields of a Table as textboxes. The
>> table has 10 fields. I have added buttons for adding a new record,
>> saving it and browsing to next, previous record, first record and last
>> record in table.
>
> It really sounds like you're writing a lot of code and going to a whole lot of
> work to do something that a simple bound form does for you all by itself. Why?
> Have you used the builtin tools (a continous Form with navigation buttons
> turned on, as they will be by default) and intentionally rejected them? If so
> why?

I see the button wizard now activated after i drag and drop a button.
The Use Control Wizards(with a wand icon) was turned off. Once i turned
it on, did drag/drop for a button it worked fine.

I did not know that a simple bound form did that, by itself.

On Thu, 24 Jun 2010 16:25:35 -0400, g <g_1@g.com> wrote:

>On 6/24/2010 2:27 PM, John W. Vinson wrote:
>> On Thu, 24 Jun 2010 13:16:10 -0400, g<g_1@g.com>  wrote:
>>
>>> I have created a form which has 5 fields of a Table as textboxes. The
>>> table has 10 fields. I have added buttons for adding a new record,
>>> saving it and browsing to next, previous record, first record and last
>>> record in table.
>>
>> It really sounds like you're writing a lot of code and going to a whole lot of
>> work to do something that a simple bound form does for you all by itself. Why?
>> Have you used the builtin tools (a continous Form with navigation buttons
>> turned on, as they will be by default) and intentionally rejected them? If so
>> why?
>
>I see the button wizard now activated after i drag and drop a button.
>The Use Control Wizards(with a wand icon) was turned off. Once i turned
>it on, did drag/drop for a button it worked fine.
>
>I did not know that a simple bound form did that, by itself.

It does... try it. You don't need ANY buttons or ANY code to do what you
describe.

You can of course use them, if there is some reason that you want to customize
the form, but it's "reinventing the wheel" to some extent. I realize that in
some other programs (Visual FoxPro for example) this is routine and normal,
but it's not required in Access.
--

             John W. Vinson [MVP]

When a button is clicked on a Access Form, I want to run a query and
display its result. I am able to do that, but my form has 12 buttons and
I am creating 12 different query objects(containing the 12 queries) for
them. So, the left had side of my window(which displays all Access
objects) which shows 12 query objects.

Now, I have to create another form which has 6 buttons which on clicked
will run 6 different queries.

1. Is there a way in Access 2007 where instead of creating another 6
query objects I can create a single query object which can contain the 6
different queries and run query 1 when button 1 is clicked, run query 2
when button 2 is clicked and so on.

2. If there is no such way, can I alleviate the issue of having multiple
query objects by running a macro when the button is clicked which runs a
query based on which button is clicked and produces the results. How
difficult would this be to do? Currently, all I have to do is create a
query object and add some SQL to it so that it produces the desired result.

Any suggestions would be appreciated.

On Wed, 30 Jun 2010 12:04:32 -0400, g <g_1@g.com> wrote:

>
>
>When a button is clicked on a Access Form, I want to run a query and
>display its result. I am able to do that, but my form has 12 buttons and
>I am creating 12 different query objects(containing the 12 queries) for
>them. So, the left had side of my window(which displays all Access
>objects) which shows 12 query objects.
>
>Now, I have to create another form which has 6 buttons which on clicked
>will run 6 different queries.
>
>1. Is there a way in Access 2007 where instead of creating another 6
>query objects I can create a single query object which can contain the 6
>different queries and run query 1 when button 1 is clicked, run query 2
>when button 2 is clicked and so on.
>
>2. If there is no such way, can I alleviate the issue of having multiple
>query objects by running a macro when the button is clicked which runs a
>query based on which button is clicked and produces the results. How
>difficult would this be to do? Currently, all I have to do is create a
>query object and add some SQL to it so that it produces the desired result.
>
>Any suggestions would be appreciated.

First off, you're not obligated to *display* the Navigation Pane at all. You
can minimize it or hide it altogether. The queries will still run if their
names aren't shown!

Secondly, do you really need 18 different queries? How are they diffrerent? If
the only change is a criterion, you may need just ONE query with a parameter.

Finally, it's generally not good design to show users query datasheets *at
all*. They aren't really designed as data presentation methods. Instead, you
could have a Form based on the query, and your button could either open that
Form, or set its Recordsource property to the appropriate query, thereby
displaying the data in a controlled, user-friendly manner.
--

             John W. Vinson [MVP]

On 6/30/2010 1:49 PM, John W. Vinson wrote:
> On Wed, 30 Jun 2010 12:04:32 -0400, g<g_1@g.com>  wrote:
>
>>
>>
>> When a button is clicked on a Access Form, I want to run a query and
>> display its result. I am able to do that, but my form has 12 buttons and
>> I am creating 12 different query objects(containing the 12 queries) for
>> them. So, the left had side of my window(which displays all Access
>> objects) which shows 12 query objects.
>>
>> Now, I have to create another form which has 6 buttons which on clicked
>> will run 6 different queries.
>>
>> 1. Is there a way in Access 2007 where instead of creating another 6
>> query objects I can create a single query object which can contain the 6
>> different queries and run query 1 when button 1 is clicked, run query 2
>> when button 2 is clicked and so on.
>>
>> 2. If there is no such way, can I alleviate the issue of having multiple
>> query objects by running a macro when the button is clicked which runs a
>> query based on which button is clicked and produces the results. How
>> difficult would this be to do? Currently, all I have to do is create a
>> query object and add some SQL to it so that it produces the desired result.
>>
>> Any suggestions would be appreciated.
>
> First off, you're not obligated to *display* the Navigation Pane at all. You
> can minimize it or hide it altogether. The queries will still run if their
> names aren't shown!

I knew that part, but it looked overwhelming to me when i was creating
it so thought
there could be a easier way.


> Secondly, do you really need 18 different queries? How are they diffrerent? If
> the only change is a criterion, you may need just ONE query with a parameter.

The queries are something like

If button 1 is clicked run below query
select Avg(Column3) from Table 1 where Column1 = Value chosen from a
combo box in the form",

If button 2 is clicked run below query
select Max(Column3) from Table 1 where Column1 = Value chosen from a
combo box in the form",

If button 3 is clicked run below query
select Min(Column3) from Table 1 where Column1 = Value chosen from a
combo box in the form

and so on. I know not a good idea as number of buttons can overwhelm the
screen quickly.

> Finally, it's generally not good design to show users query datasheets *at
> all*. They aren't really designed as data presentation methods. Instead, you
> could have a Form based on the query, and your button could either open that
> Form, or set its Recordsource property to the appropriate query, thereby
> displaying the data in a controlled, user-friendly manner.

Yes, the form runs a query(its record source property is set to a query)
and users don't view the datasheets at all.

Thanks for your suggestions, though. Also, I posted this in the wrong
thread.

g <g_1@g.com> wrote in news:9WJWn.378$Zp1.278@newsfe15.iad:

> 1. Is there a way in Access 2007 where instead of creating another
> 6 query objects I can create a single query object which can
> contain the 6 different queries and run query 1 when button 1 is
> clicked, run query 2 when button 2 is clicked and so on.
>
> 2. If there is no such way, can I alleviate the issue of having
> multiple query objects by running a macro when the button is
> clicked which runs a query based on which button is clicked and
> produces the results. How difficult would this be to do?
> Currently, all I have to do is create a query object and add some
> SQL to it so that it produces the desired result.

You could try a listbox that lists the queries, and a command button
that runs the query selected in the listbox. There are complexities
in that that are not amenable to solution with macros, but it's not
insurmountable.

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/