Double click to open form and create new record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Damo G - 17 Apr 2007 07:54 GMT

Hi team,

Have scoured all of the posts and tried several different things for days
but just can't seem to nut this one out.

I have a form (FrmSeedBankStockAvailable) in datasheet view that lists seed
lots by BatchID. I want to be able to double click on the record selector
for a particular batch and have a form (DialogSeedBankWithdrawal) open so
that I can make a new withdrawal entry, with the BatchID already entered from
the selected record. I know that to do this I need to use the forms'
DblClick property. I have been using the following code (and tried
variations of from other posts).

DoCmd.OpenForm "DialogSeedBankWithdrawal", , , "[BatchID] = " & Me!BatchID,
acFormAdd

Some of the time I just get a blank form, and other times when I got the
code close it would ask for the paratmeter but the particular batch ID would
be in the pop up. I know it is probably something very simple.

Thanks in advance for anyone who can help.

Damo G

Reply to this Message

Ofer Cohen - 17 Apr 2007 09:36 GMT

There is no much point on openning the form with a criteria (WhereCondition)
if you open the form with acFormAdd, which mean open the form for data input
only.

Instead open the form without a criteria, and pass the Me!BatchID to the
next form using the OpenArgs

DoCmd.OpenForm "DialogSeedBankWithdrawal", , , , , , Me.BatchID

On the OnLoad event of the DialogSeedBankWithdrawal form write the code

If Me.NewRecord And Not IsNull(Me.OpenArgs) Then
Me.BatchID = Me.OpenArgs
End If

Signature

Good Luck
BS"D

> Hi team,
>
[quoted text clipped - 19 lines]
>
> Damo G

Reply to this Message

Damo G - 18 Apr 2007 01:18 GMT

Thanks for the quick response Ofer.

I cut and pasted your code. Upon execution the DialogSeedBankWithdrawal
form did open, but it didn't create a new entry and just listed all of the
withdrawal records none of which matched the BatchID. Do I need to change
the properties of the DialogSeedBankWithdrawal form or is there a bit missing
from the code?

Thanks and regards,

Damo G

> There is no much point on openning the form with a criteria (WhereCondition)
> if you open the form with acFormAdd, which mean open the form for data input
[quoted text clipped - 34 lines]
> >
> > Damo G

Reply to this Message

Ofer Cohen - 18 Apr 2007 09:56 GMT

Sorry, I missed the "acFormAdd" in the OpenForm command line

DoCmd.OpenForm "DialogSeedBankWithdrawal", , , , , , Me.BatchID,acFormAdd

Good Luck
BS"D

> Thanks for the quick response Ofer.
>
[quoted text clipped - 46 lines]
> > >
> > > Damo G

Reply to this Message

Ofer Cohen - 18 Apr 2007 10:04 GMT

Sorry, wrong order

DoCmd.OpenForm "DialogSeedBankWithdrawal", , , ,acFormAdd , , Me.BatchID

Good Luck
BS"D

> Thanks for the quick response Ofer.
>
[quoted text clipped - 46 lines]
> > >
> > > Damo G

Reply to this Message

Damo G - 18 Apr 2007 10:36 GMT

Thanks Ofer,

With my limited knowledge I anticipated it was missing before you replied so
I tried that and it still didn't work. Your response though has proved that
I was right and the problem must have lain elsewhere. I went back to the
DialogSeedBankWithdrawal and had a look at all of my settings. I eventually
discovered where my problem lay. My forms record source related to a query
that contained only the fields from the Withdrawal table. For some reason
(which I investigated but couldn't acertain - perhaps you can help) when I
open the query it won't allow new entries. I changed the record source to
the Withdrawal table and now everything works as it should. Wouldn't have
been able to sort this one out without you.

Thanks again.

Damo G

> Sorry, wrong order
>
[quoted text clipped - 64 lines]
> > > >
> > > > Damo G

Reply to this Message

Ofer Cohen - 18 Apr 2007 11:22 GMT

Sometimes when you link few tables in a query, it doesn't allow adding or
changing records, it will prompt you with the message that this record is not
updatetable (especialy when you link two tables not by the key).

Sometimes I'm changing the UniqueProperty of the query to Yes it make it
possible to update or add records.

If that doesn't help, I'm removing tables from the query, and in the form I
use Combo's Or SubForms instead to display the desired records.

Signature

Good Luck
BS"D

> Thanks Ofer,
>
[quoted text clipped - 81 lines]
> > > > >
> > > > > Damo G

Reply to this Message

Damo G - 19 Apr 2007 01:04 GMT

The problem in that was my query related to only one table. I finally
discovered the cause by recreating the query and comparing it with my
original query. For some reason I had totals enabled for the query and it
was grouping by all of the fields. As soon as I disabled this everything
returned to normal.

Thanks once again.

Damo G

> Sometimes when you link few tables in a query, it doesn't allow adding or
> changing records, it will prompt you with the message that this record is not
[quoted text clipped - 91 lines]
> > > > > >
> > > > > > Damo G

Reply to this Message