open arguments.

[Guest]

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Bill,

Stop grunting! At first glance all seems OK. I assume it's the
assignment statement for Me![Reference] that triggers the error.

Two thoughts however.

The open arguments passed by DoCmd.OpenForm is a string. Access should do
all the type conversion for you, but for you to be 'in control' I would
suggest passing CStr(Me![Enquiry/Quote]) and making the second term in the
assignment, CLng(Me.OpenArgs). Change the second function to CInt or
whatever is appropriate. However I don't think this will solve your problem.

The second and more probable cause of the error is that Access has
identified [Reference] as a control whose value it maintains. Is the control,
Reference bound to something in the underlying recordset and hence to a table
column? How is everything specified throughout that linkage?

A third possibility and equally likely is that you have based your form on a
query or view and the nature of that query makes it logically impossible for
Access to update the underlying table.

I can't think of anything else for the moment.

Regards,

Rod

PS The multiple use of DLookUp is not an efficient way of retrieving DB
data. Much better to open a recordset and then move the elements of the
recordset to the form controls.

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

 

[Guest]

Thanks Rod,
Appreciate the input.
Will digest and assimilate.
The feild reference is part of the main table.
The data for "refernece" comes from an autonumber generated on the original
form. This Autonumber feild is the primary key - oooops.
If I create another feild called "ID" in the table and designate this the
primary key will this help?
--
Regards
Bill

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Thanks Rod,
To use the recordset method is something that I am not familiar with.
Please point me in the direction to learn about this.
I have no formal training, just what I have read and picked up.
--
Regards
Bill

Hi Bill,

Stop grunting! At first glance all seems OK. I assume it's the
assignment statement for Me![Reference] that triggers the error.

Two thoughts however.

The open arguments passed by DoCmd.OpenForm is a string. Access should do
all the type conversion for you, but for you to be 'in control' I would
suggest passing CStr(Me![Enquiry/Quote]) and making the second term in the
assignment, CLng(Me.OpenArgs). Change the second function to CInt or
whatever is appropriate. However I don't think this will solve your problem.

The second and more probable cause of the error is that Access has
identified [Reference] as a control whose value it maintains. Is the control,
Reference bound to something in the underlying recordset and hence to a table
column? How is everything specified throughout that linkage?

A third possibility and equally likely is that you have based your form on a
query or view and the nature of that query makes it logically impossible for
Access to update the underlying table.

I can't think of anything else for the moment.

Regards,

Rod

PS The multiple use of DLookUp is not an efficient way of retrieving DB
data. Much better to open a recordset and then move the elements of the
recordset to the form controls.

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Bill,

Where do you enter your coding? Do you use Macros or the VBA (Visual Basic
for Applications) window? What I am about to discuss applies to VBA.

Each Access Form may be (usually is) bound to a Recordset and each Recordset
may be (usually is) bound to a database (in most cases this is the Jet
database that is an integral component of Access). If you start off your
Form design by using the Access wizard then you are prompted to nominate a
Table or a Query. By selecting one of these objects what you are really
doing is telling Access how to generate the Recordset to which the Form is
bound. If you examine the properties dialog box for a Form you will see on
the Data tab a property called RecordSource but no property for Recordset.
Yet Recordset is a property and can be set programmatically. If you read the
help it mentions that setting the Recordset property will alter the
RecordSource property and vice versa. The difference? Well Recordset is a
reference to the actual Recordset object whereas RecordSource is the name of
a table, query or an explicit SQL (System Query Language) string.

So in essence a Recordset is an interface between your Form and the
database. Access handles the synchronisation of data and is even 'clever'
enough to show you updates made by others as they make them provided you set
all the right properties. You rarely work directly with the Form's Recordset
but often work with a copy called a Clone. You can also (and often do) work
with a Recordset that has no Form bound to it; this is the best way of
programmatically interfacing with your database.

Now I come to a dilemma. There are two similarly abbreviated but separate
methods (Object Models if you want the official term) of interfacing to your
data: DAO (Data Access Objects) and ADO (ActiveX Data Objects). That's the
bad news. The good news is that you can have both models active in the same
project and use either depending upon what is best for the situation. If you
do this then you must explicitly reference every object you use or Access
will get 'confused.' For example a Recordset class exists in both models and
so you must define ADODB.Recordset or DAO.Recordset. While on this topic I
should mention that to use any object model you must have an active reference
to the library for that model. From the VBA window go to Tools/References
from the menu bar. The checked entries are the active references. In Access
v2003 both DAO and ADO are referenced. In recent versions only ADO is
referenced and before that only DAO (since ADO did not exist).

DAO is the access method that is closely integrated with the Jet database
engine. It uses a lot of native (Jet specific) routines. In most cases DAO
methods will out-perform ADO but DAO has limited capability of interfacing
with other data sources.

ADO is a more generic model. It can interface with just about any data
source that conforms to its standards and that includes non DB sources! It
does this through a Connection and one such connection just happens to be the
Jet database.

OK, this has been an introduction and background. I hope knowing the above
the VBA help will make more sense. Search on Recordset from the Help in the
VBA window (not the Access window) and read up on methods, properties, etc.
After that try to lay your hands on a text book with VBA and/or Access
Programming in the title.

Microsoft are withdrawing support for Visual Basic in preference to the Net
offerings. I don't know where this leaves VBA but all MS Office systems are
programmable with VBA - Word, Outlook, Visio, Excel, etc., not just Access.
So I think VBA will be around for a time.

Rod

Thanks Rod,
To use the recordset method is something that I am not familiar with.
Please point me in the direction to learn about this.
I have no formal training, just what I have read and picked up.
--
Regards
Bill

Hi Bill,

Stop grunting! At first glance all seems OK. I assume it's the
assignment statement for Me![Reference] that triggers the error.

Two thoughts however.

The open arguments passed by DoCmd.OpenForm is a string. Access should do
all the type conversion for you, but for you to be 'in control' I would
suggest passing CStr(Me![Enquiry/Quote]) and making the second term in the
assignment, CLng(Me.OpenArgs). Change the second function to CInt or
whatever is appropriate. However I don't think this will solve your problem.

The second and more probable cause of the error is that Access has
identified [Reference] as a control whose value it maintains. Is the control,
Reference bound to something in the underlying recordset and hence to a table
column? How is everything specified throughout that linkage?

A third possibility and equally likely is that you have based your form on a
query or view and the nature of that query makes it logically impossible for
Access to update the underlying table.

I can't think of anything else for the moment.

Regards,

Rod

PS The multiple use of DLookUp is not an efficient way of retrieving DB
data. Much better to open a recordset and then move the elements of the
recordset to the form controls.

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
....
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
....
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Thanks Rod,
I appreciate all.
You've got a sense of humour and thats fantastic.
I'll digest and put to good use over the weekend.
Cheers
--
Warm Regards
Bill

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Bill,

A couple more things. The Set rst statement should ideally follow the If
statement; you don't want to try to form a recordset if there are no open
arguments. The If statement itself should read:

If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then

If Access bitches about the NewRecord property (or it does not work) move
all the code to the form's Load event. I don't know at what point in the
sequence Access sets this property (Load comes after Open). If it's not set
at Load time then back to the proverbial drawing board.

It's not necessary to create three versions of the same form unless you use
DoCmd.OpenForm and want more than one copy visible at the same time. There
is a technique using VBA by which you can open the same form as many times as
you like each with its own separate and different properties. However that's
a side issue for now.

The problem is that Access refuses to load a value into Me![Reference]. I've
no idea why not. There is a myriad of reasons why this might be and you must
embark on a series of tests to narrow down the cause.

1. Open the original table from the Access database window and try to add a
new record. Can you add a value to the Reference column?

2. Is the form based directly on the table or on a query based on the table?
If the latter open the query and try to add a new record. Can you add a value
to Reference?

3. Delete the control Reference from your form and re-add it, preferably
using drag 'n drop from the field list or selecting Reference from the
Control Source pull-down list. Does the problem persist?

4. Use the Access wizard to create a new test form based on the table/query.
Use the record navigation to go to a new record. Can you add a value in
Reference?

5.......

Sorry, these things happen and it's a long tedious process to track them
down - but that's the way we learn, isn't it?

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill


:

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Bill,

Just realised there is an Access object called Reference. Try changing your
field/control name to say Ref.

Rod

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill


:

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Rod,
Still getting and error message.
I have changed the table to read "Ref" and all forms where this field is
read "REf"
I have revised the code to,
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Ref] = rst.Fields![Enquiry/Quote]
Else
'do something
End If

The eror is still you cannot assign a value to this control/object.
Me![Ref] = rst.Fields![Enquiry/Quote]

On the edit form the contol code is.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]


Is there another way to open a form and copy information to it?
--
Regards
Bill

Bill,

Just realised there is an Access object called Reference. Try changing your
field/control name to say Ref.

Rod

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

:

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill


:

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

Dim strFilter As String
'Evaluate filter before it is passed to Dlookup function
strFilter = "[Reference]=" & Me![Enquiry/Quote].Value
'Update the Form Controls from the dlookup

Me![Reference] = Nz(DLookup("[Enquiry/quote]", "[Sales Enquiry
Register]", strFilter))
Me![CompanyName] = Nz(DLookup("[CompanyName]", "[Sales Enquiry
Register]", strFilter))
Me![Phone No:] = Nz(DLookup("[Phone No:]", "[Sales Enquiry Register]",
strFilter))
Me![Fax No] = Nz(DLookup("[Fax No]", "[Sales Enquiry Register]",
strFilter))
Me![PostBox] = Nz(DLookup("[Postbox]", "[Sales Enquiry Register]",
strFilter))
Me![Street] = Nz(DLookup("[Street]", "[Sales Enquiry Register]",
strFilter))
Me![Suburb] = Nz(DLookup("[Suburb]", "[Sales Enquiry Register]",
strFilter))
Me![City] = Nz(DLookup("[City]", "[Sales Enquiry Register]", strFilter))
Me![State] = Nz(DLookup("[State]", "[Sales Enquiry Register]", strFilter))

Any ideas.
Pointers in the right direction.
I've even tried just opening the form and having an activation button to
copy the information across.

 

[Guest]

Hi Bill,

Why are you persisting with If Me.OpenArgs ......? It means nothing. It
should read If Me.NewRecord ....... Anyway as you now have a form version
specifically for data entry and only data entry the If statement is somewhat
redundant.

Yes there is another way to open forms but I suspect you will still get the
error. Have you tried all the tests I suggested and if so what was the
result? At the moment it's like trying to hit a dartboard while blindfolded;
we must narrow down the Is/IsNot qualification. Are you absolutely sure it is
[Reference] that is causing the problem?

Regards,

Rod

Hi Rod,
Still getting and error message.
I have changed the table to read "Ref" and all forms where this field is
read "REf"
I have revised the code to,
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Ref] = rst.Fields![Enquiry/Quote]
Else
'do something
End If

The eror is still you cannot assign a value to this control/object.
Me![Ref] = rst.Fields![Enquiry/Quote]

On the edit form the contol code is.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]


Is there another way to open a form and copy information to it?
--
Regards
Bill

Bill,

Just realised there is an Access object called Reference. Try changing your
field/control name to say Ref.

Rod

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill


:

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

:

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill


:

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs

 

[Guest]

Thanks Rod - honestly thanks,
I will comply.
Yes, my persistance is futile because I Know no better, have no further
knowledge to fall back on.
Will digest and modify my approach and solution from your comment
--
Best Regards
Bill

Hi Bill,

Why are you persisting with If Me.OpenArgs ......? It means nothing. It
should read If Me.NewRecord ....... Anyway as you now have a form version
specifically for data entry and only data entry the If statement is somewhat
redundant.

Yes there is another way to open forms but I suspect you will still get the
error. Have you tried all the tests I suggested and if so what was the
result? At the moment it's like trying to hit a dartboard while blindfolded;
we must narrow down the Is/IsNot qualification. Are you absolutely sure it is
[Reference] that is causing the problem?

Regards,

Rod

Hi Rod,
Still getting and error message.
I have changed the table to read "Ref" and all forms where this field is
read "REf"
I have revised the code to,
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Ref] = rst.Fields![Enquiry/Quote]
Else
'do something
End If

The eror is still you cannot assign a value to this control/object.
Me![Ref] = rst.Fields![Enquiry/Quote]

On the edit form the contol code is.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]


Is there another way to open a form and copy information to it?
--
Regards
Bill

Bill,

Just realised there is an Access object called Reference. Try changing your
field/control name to say Ref.

Rod

:

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill


:

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

:

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill


:

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.

 

[Guest]

Bill, Something wrong with the board; I posted a reply to your last message
immediately but I can find no trace of it. Give me a while and I'll do it
again.
Rod

Hi Rod,
Still getting and error message.
I have changed the table to read "Ref" and all forms where this field is
read "REf"
I have revised the code to,
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Ref] = rst.Fields![Enquiry/Quote]
Else
'do something
End If

The eror is still you cannot assign a value to this control/object.
Me![Ref] = rst.Fields![Enquiry/Quote]

On the edit form the contol code is.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]


Is there another way to open a form and copy information to it?
--
Regards
Bill

Bill,

Just realised there is an Access object called Reference. Try changing your
field/control name to say Ref.

Rod

Thanks Rod,
Sorry to trouble you.
I have set up 3 forms now.
The original to add a complete new quote, one to edit a quote and one to
open from the edit form to copy the data and create a new revision, so no
form is taking focus or trying to add data whilst the other is open.
The VBA for the "OnOpen" in the 3rd form for coping now reads,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register] "
strsql = strsql & "WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
added the space after the bracket and the other change.
A runtime error now occurs at
Me![Reference] = rst.Fields![Enquiry/Quote]
"You cannot assign a value to this object"
I have tried
Me![CompanyName] = rst.Fields![CompanyName]
Which is another field to which data would also be copied - with the same
result and error message with the same message.

Any help appreciated as always.

I've used open args before to copy information from an underlying form to a
completely unrelated form with its own table.
This time I am trying to use a common table and take a set of data and add
it too a new line in in the table so to speak and things arn't going
according to plan.


--
Warm Regards
Bill


:

Hi Bill,

There're a couple of errors with your strsql assignment.

First you should include a space after the final bracket on the first line.
As it is, when the lines are concatenated the keyword WHERE will abut the
bracket and I don't know what the SQL parser will make of that.

What is probably causing the error message is the second line. This should be
"WHERE [Enquiry/Quote] = " & CLng(Me.OpenArgs)

Note that the literal part ends after the equals sign to which is
concatenanted the value of OpenArgs converted to a numeric long data type.

Get back if this does not work.

Rod

:

Hi Rod,
I've tried the follwing,
Dim dbs As Database
Dim rst As Recordset
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT * FROM [Sales Enquiry Register]"
strsql = strsql & "WHERE [Enquiry/Quote] = openargs"

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)
If Me.OpenArgs And Len(Trim(Nz(Me.OpenArgs, ""))) > 0 Then
'do something
Me![Reference] = rst.Fields![Enquiry/Quote]
Else
'do something
End If
It is in the "Onopen" of the form.
I'm getting too few parameters - expect one in the follwing line.
Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

I'm giving it a go - any ideas?

--
Warm Regards
Bill


:

Hi Bill,

Hope you enjoyed my diatribe in response to one of your other posts.

Let's take this step by step.

DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

This opens the form specified by stDocName in data entry mode and passes the
value of [Enquiry/Quote] to it as a string. Now something to note here that
may or may not be important. The DoCmd methods 'simulate' the Access
actions. If the form specified by stDocName is already Open/Loaded then DoCmd
simply makes that copy visible and brings it to the front changing its data
entry mode as necessary; it does not open a new copy of the form. If the
form was Dirty then an update is triggered and this update may fail
validation. But let's assume the stDocName form was not loaded and a fresh
new copy is opened and loaded in data entry mode with no problems.

As the form opens in data entry mode all fields will be empty or show their
default values. The autonumber primary key field will show something like
'autonumber' that will immediately change to the next number in sequence the
moment anything changes on the form.

OK, what you want to do is to copy all the quotation data from a previous
quotation referenced by the value [Enquiry/Quote] passed in OpenArgs. This
reference value should be stored in the field named [Reference].

As always there is more than one way of skinning a cat. You have chosen to
retrieve this data from the database. I would first dimension a numeric
variable to hold the old key and place the value of OpenArgs in it. You
don't have to do this and could use OpenArgs directly but I just think it's
'cleaner.'

But before you do anything else you must work out how your system 'knows'
that this is a situation where it should retrieve the data. I assume the form
can be opened in other modes and can be opened in data entry mode when there
is no previous data. One way you could determine this - and only you can
tell whether this is foolproof - is to test for data entry mode and the
presence of OpenArgs. So choose the Open or Load event of your form and
encase all the data population code with an If statement.

Dim lngReference as Long
If Me.NewRecord And Len(Trim(Nz(Me.OpenArgs,""))) > 0 then
lngReference = CLng(Me.OpenArgs)
...
End If

Let's now think about what goes in place of the ellipsis above.

You want to store your reference so poke it into the reference field.

Me![Reference] = lngReference

Everything else comes from the [Sales Enquiry Register] table so define and
open a Recordset that will contains the desired data. I am giving you the
ADO solution - I'm a bit rusty on DAO but if you offer me enough reward I'll
give you that as well.

Dim rstSER as ADODB.Recordset 'Best put this with other Dims at top of code
Set rstSER = New ADODB.Recordset 'Instantiate this object
rstSER.ActiveConnection = CurrentProject.Connection 'Establish the data source
rstSER.LockType = adLockReadOnly
rstSER.Open "SELECT * FROM [Sales Enquiry Register] WHERE [Enquiry/Quote] =
" & lngReference
Me![CompanyName] = rstSER![CompanyName]
...
rstSER.Close
Set rstSER = Nothing

A few words of explanation. The fifth line above has text-wrapped; enter it
all on the same line. In the company name assignment and following use the
form control name on the left of the assignment and the table column name on
the right. Normally these are the same.

There is no check that a record has actually been returned.

RstSER.Close closes the recordset but does not destroy it. Setting the
reference to nothing means that if there are no other references to it the
memory it occupies will be reclaimed.

Enough for now.

Regards,

Rod

:

Hi Rod,
Your help is very much appreciated.
A little more information.
The original form is linked to a table called 'Sales".
If a quote is to be revised it requires a new identifying number which will
be an autonumber (feild "Enquiry/Quote").

The information from the original form is copyed to a new form.
The original idenitying number of the original form (feild "enquiry/Quote")
becomes the reference (Field "Refernece") for the new form, but it will have
a new identifying number.

The base table "Sales" has a field called reference which the forms are
linked too.
With the new form, there will be an autonumber generated and in the refernce
feild a number from the previous form.

--
Regards
Bill


:

Bill,

Taking a closer look there's something weird about the logic and use of
[Reference]. On opening the new form you place the OpenArgs value in this
control. You then overwrite this value with a DLookUp searching for a match
on Me![Enquiry/Quote].Value. What is the value of [Enquiry/Quote] at this
time? Why do you need to overwrite the value you have so carefully passed
through the open arguments? I suspect your filter should be something like
"[Reference] = " & [Reference]

But I don't think this is related to the error. [Reference] is not bound to
the primary key by any chance, is it?

Rod

:

Hi,
I have a form with a button which allows the user to create a revision of a
quote - that is a new form is opened.
The idea is that a new form opens with a new number and there is a field
that refernces the original quote form.
The button has code,
" stDocName = "Sales Enquiry Register1"


DoCmd.OpenForm stDocName, , , , acFormAdd, , Me![Enquiry/Quote]

I'm using the existing number as the opening argument to transfer other
information.

The form opens and I get a error that the field refernce cannot be given
this value

Dim dbs As DAO.Database
Set dbs = CurrentDb

Me![Reference] = Me.OpenArgs