Query vs Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need for access to open my input form as a new record, however if I have
the form pulling information off of a query vs a table is keeps giving me an
error of "You can't go toi that specific record". Is there a way to get
access to create a new record while using a query?
 
Your query is not updateable. Search Help for "updateable" to see some of
the reasons why this might be the case.
 
OK. So how can I get the information in "unbound" objects to store in the
table? Right now I cannot get it to do that except by using the query?

I am sort of new to access so I appreciate any hekp

Ray
 
You can bind a control on a form to a table just as easily as you can to a
query. I think you need to describe your situation a bit more specifically.
 
-----Original Message-----
I need for access to open my input form as a new record, however if I have
the form pulling information off of a query vs a table is keeps giving me an
error of "You can't go toi that specific record". Is there a way to get
access to create a new record while using a query?
.
Click to expand...

I'm Not sure what vesion of access you are using and
correct me if I'm wrong But if you are using Access 2000
and beyond you can set the forms Data Entry Property to
yes and the form will open for data entry only at a new
record nor matter if you use a Table or a query as your
souce.

Here's a bit of code I Uuesd to update a record using DAO

Dim rcdContacts As DAO.Recordset

Set rcdContacts = CurrentDb.OpenRecordset("SELECT * From
[Contacts]")


rcdContacts.AddNew 'opens the record set for new Entry

rcdContacts![Contact_Name] = Me.ConName
rcdContacts![Contact_AddressLn1] = Me.AddressLn1
rcdContacts![Contact_AddressLn2] = Me.AddressLn2
rcdContacts![Contact_AddressLn3] = AddressLn3
rcdContacts![Contact_postcode] = Me.postcode
rcdContacts![Phone Number] = PhoneNumber
rcdContacts![Mobile_No1] = Me.MobileNo1
rcdContacts![Mobile_no2] = Me.MobileNo2
rcdContacts![Mobile_No3] = Me.MobileNo3
rcdContacts![Email_Address] = Me.EmailAddress
rcdContacts![Customer_ID] = Me.Customer_ID

rcdContacts.Update ' updates the recordset to the
contacts table

Anything with Me. in front of it is a unbound textbox of
my input form

I hope this is of use to you
 
Well I have a form that a user can enter information (stats and time amount).
The form takes all this information and then add the data in 3 sperate boxes.
! box adds the total amoutn of tickets completed. Second box is a sub total
of hours from the intial reporting items and the third box is for a total of
hours to include meeting, training etc. This form is for daily work stats and
will be used by multiple users.

Ray
 
Thanks John. I tried turning on the Data Entry to on but still get the same
error. Be advised I am new to Access so a lot of this information is new to
me.

Thanks,

Ray

JohnG said:
-----Original Message-----
I need for access to open my input form as a new record, however if I have
the form pulling information off of a query vs a table is keeps giving me an
error of "You can't go toi that specific record". Is there a way to get
access to create a new record while using a query?
.
Click to expand...

I'm Not sure what vesion of access you are using and
correct me if I'm wrong But if you are using Access 2000
and beyond you can set the forms Data Entry Property to
yes and the form will open for data entry only at a new
record nor matter if you use a Table or a query as your
souce.

Here's a bit of code I Uuesd to update a record using DAO

Dim rcdContacts As DAO.Recordset

Set rcdContacts = CurrentDb.OpenRecordset("SELECT * From
[Contacts]")


rcdContacts.AddNew 'opens the record set for new Entry

rcdContacts![Contact_Name] = Me.ConName
rcdContacts![Contact_AddressLn1] = Me.AddressLn1
rcdContacts![Contact_AddressLn2] = Me.AddressLn2
rcdContacts![Contact_AddressLn3] = AddressLn3
rcdContacts![Contact_postcode] = Me.postcode
rcdContacts![Phone Number] = PhoneNumber
rcdContacts![Mobile_No1] = Me.MobileNo1
rcdContacts![Mobile_no2] = Me.MobileNo2
rcdContacts![Mobile_No3] = Me.MobileNo3
rcdContacts![Email_Address] = Me.EmailAddress
rcdContacts![Customer_ID] = Me.Customer_ID

rcdContacts.Update ' updates the recordset to the
contacts table

Anything with Me. in front of it is a unbound textbox of
my input form

I hope this is of use to you
Click to expand...
 
you may want to check your query to see if you have any
arguments in the query's Criteria column

make sure your form is set to dynaset (Form Property)

remove any filters you may have assigned to the form

worse case scenario create a new Form and start afresh!

I hope this can help JohnG
-----Original Message-----
Thanks John. I tried turning on the Data Entry to on but still get the same
error. Be advised I am new to Access so a lot of this information is new to
me.

Thanks,

Ray

JohnG said:
-----Original Message-----
I need for access to open my input form as a new
Click to expand...
record,
however if I have
the form pulling information off of a query vs a
Click to expand...
table
is keeps giving me an
error of "You can't go toi that specific record". Is there a way to get
access to create a new record while using a query?
.
Click to expand...

I'm Not sure what vesion of access you are using and
correct me if I'm wrong But if you are using Access 2000
and beyond you can set the forms Data Entry Property to
yes and the form will open for data entry only at a new
record nor matter if you use a Table or a query as your
souce.

Here's a bit of code I Uuesd to update a record using DAO

Dim rcdContacts As DAO.Recordset

Set rcdContacts = CurrentDb.OpenRecordset("SELECT * From
[Contacts]")


rcdContacts.AddNew 'opens the record set for new Entry

rcdContacts![Contact_Name] = Me.ConName
rcdContacts![Contact_AddressLn1] = Me.AddressLn1
rcdContacts![Contact_AddressLn2] = Me.AddressLn2
rcdContacts![Contact_AddressLn3] = AddressLn3
rcdContacts![Contact_postcode] = Me.postcode
rcdContacts![Phone Number] = PhoneNumber
rcdContacts![Mobile_No1] = Me.MobileNo1
rcdContacts![Mobile_no2] = Me.MobileNo2
rcdContacts![Mobile_No3] = Me.MobileNo3
rcdContacts![Email_Address] = Me.EmailAddress
rcdContacts![Customer_ID] = Me.Customer_ID

rcdContacts.Update ' updates the recordset to the
contacts table

Anything with Me. in front of it is a unbound textbox of
my input form

I hope this is of use to you
Click to expand...
.
Click to expand...
 
Ok... followed your suggestions, but no go. The 3 fields I need are unbound
as I need to put calculations in them. How can I get the table to reflect the
calulations in the field?

johnG said:
you may want to check your query to see if you have any
arguments in the query's Criteria column

make sure your form is set to dynaset (Form Property)

remove any filters you may have assigned to the form

worse case scenario create a new Form and start afresh!

I hope this can help JohnG
-----Original Message-----
Thanks John. I tried turning on the Data Entry to on but still get the same
error. Be advised I am new to Access so a lot of this information is new to
me.

Thanks,

Ray

JohnG said:
-----Original Message-----
I need for access to open my input form as a new record,
however if I have
the form pulling information off of a query vs a table
is keeps giving me an
error of "You can't go toi that specific record". Is
there a way to get
access to create a new record while using a query?
.


I'm Not sure what vesion of access you are using and
correct me if I'm wrong But if you are using Access 2000
and beyond you can set the forms Data Entry Property to
yes and the form will open for data entry only at a new
record nor matter if you use a Table or a query as your
souce.

Here's a bit of code I Uuesd to update a record using DAO

Dim rcdContacts As DAO.Recordset

Set rcdContacts = CurrentDb.OpenRecordset("SELECT * From
[Contacts]")


rcdContacts.AddNew 'opens the record set for new Entry

rcdContacts![Contact_Name] = Me.ConName
rcdContacts![Contact_AddressLn1] = Me.AddressLn1
rcdContacts![Contact_AddressLn2] = Me.AddressLn2
rcdContacts![Contact_AddressLn3] = AddressLn3
rcdContacts![Contact_postcode] = Me.postcode
rcdContacts![Phone Number] = PhoneNumber
rcdContacts![Mobile_No1] = Me.MobileNo1
rcdContacts![Mobile_no2] = Me.MobileNo2
rcdContacts![Mobile_No3] = Me.MobileNo3
rcdContacts![Email_Address] = Me.EmailAddress
rcdContacts![Customer_ID] = Me.Customer_ID

rcdContacts.Update ' updates the recordset to the
contacts table

Anything with Me. in front of it is a unbound textbox of
my input form

I hope this is of use to you
Click to expand...
.
Click to expand...
Click to expand...
 
Ok... followed your suggestions, but no go. The 3 fields I need are unbound
as I need to put calculations in them. How can I get the table to reflect the
calulations in the field?
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

Appreciate the info but the data needs to be stored as it is the basis of a
report being generated to check stats and measure productivity. What I am
running into is I need the calculations in the form so they can be stored,
but I need the form to open as a new record. I can't seem to do this using a
query, but need the query for the calulations.

Ray
 
Dear Ray:

The fact that this information is "the basis of a report being
generated to check stats and measure productivity" has nothing to do
with the need to store it. Calculate it each time you need the
report, as John suggested. Virtually all experts on database are
agreed on this as a rule that should almost never be broken, and there
are no indications your requirements support an exception to this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

I understand that concept but i need to calculate the info on the form
before it is saved. The rep entering their data needs to verify the info.
Using formulas in the form will not allow me to use bind it to the table as
it is a unbound field. How can I get the calculated information to show in
the table?
 
Dear Ray:

The whole point is that you need to calculate the information whenever
you need to see it, but NOT save it. Calculate it so the rep can
verify it, but don't save it. Don't "bind it to the table" at all.
The calculated information won't show in the table. Why would you
want it to? You can calculate it and display it whenever it is
needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Ok!... So leave the raw data in the table, with no calculations. Leave the
calculations on the form so they can fill in the required feilds to verify
the information. Then when they need to pull the data have the query do the
calculations. Correct?
 
Dear Ray:

Yes, I think you've got it. As a rule, anything that can be derived
should be calculated at the last moment, just when you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks everyone for the help!

Tom Ellison said:
Dear Ray:

Yes, I think you've got it. As a rule, anything that can be derived
should be calculated at the last moment, just when you need it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Back
Top