insert values into table from ACCESS FORM

[mls via AccessMonster.com]

I am new to ACCESS programming. I have to perform 2 tasks:
question1: I want to insert all the field values ( around 30) from an ACCESS
FORM when some one checks on field called FINAL , which is a checkbox. This I
want to do with wild card rather than typing all the 30 fields.

question2: How can I append data into a table programmetically when a button
is clicked, because when I use DoMenuItem it is is showing strange number ,
hard to decode.

I am using ACCESS 2003 and I am looking for syntax .

Thanks in advance.

 

[John W. Vinson]

I am new to ACCESS programming. I have to perform 2 tasks:
question1: I want to insert all the field values ( around 30) from an ACCESS
FORM when some one checks on field called FINAL , which is a checkbox. This I
want to do with wild card rather than typing all the 30 fields.

question2: How can I append data into a table programmetically when a button
is clicked, because when I use DoMenuItem it is is showing strange number ,
hard to decode.

I am using ACCESS 2003 and I am looking for syntax .

Thanks in advance.

WHY are you doing it the hard way????

It would require *no code at all*, and no checkbox either, if you simply use a
form bound to the table, or to a Query based on the table. You could put code
in the form's Before Update event to cancel the update unless [FINAL] is
checked.

Have you examined and consciously rejected the straightforward, normal
approach? Why?

 

[mls via AccessMonster.com]

John, Thanks for raising the question."consciously rejected the
straightforward, normal
approach". Actually I use menu driven.. but what happened was my form bound
table is getting updated instead of appending.

I have to store atleast 4 test results( rows) for each patient. When some one
chages the test1 to test2 and entered the results for existing person the
form bound table( Transaction table) is getting updated rather than appended.
i..e patient id, test2 ( instead test1) and the new results are store for
that patient over writting the old values. ( remove the primary key on
patient id even then duplicates are not stored)

I placed "Final" check box , just to save the final results in Master table .
One record per patient.
My transaction will have atleast 4 records per patient.


I just have to dump all the 70 values from the FORM into transaction table
every time when a change is made and just once in Master table. Is there a
easy solution with out coding?
As I said I am new to ACCESS so could not even figure out the simple way to
do these things.

Again thank you very much for responding to my questions.

I am new to ACCESS programming. I have to perform 2 tasks:
question1: I want to insert all the field values ( around 30) from an ACCESS

[quoted text clipped - 8 lines]

Thanks in advance.

WHY are you doing it the hard way????

It would require *no code at all*, and no checkbox either, if you simply use a
form bound to the table, or to a Query based on the table. You could put code
in the form's Before Update event to cancel the update unless [FINAL] is
checked.

Have you examined and consciously rejected the straightforward, normal
approach? Why?

 

[John W. Vinson]

John, Thanks for raising the question."consciously rejected the
straightforward, normal
approach". Actually I use menu driven.. but what happened was my form bound
table is getting updated instead of appending.

Forms are perfectly appropriate for doing *either* updating or appending,
depending on how they are designed and used.

I have to store atleast 4 test results( rows) for each patient. When some one
chages the test1 to test2 and entered the results for existing person the
form bound table( Transaction table) is getting updated rather than appended.
i..e patient id, test2 ( instead test1) and the new results are store for
that patient over writting the old values. ( remove the primary key on
patient id even then duplicates are not stored)

How and where are you "changing the test"? It sounds like you should have at
least three tables: Patients, with one row for each patient; Tests, with (I'm
guessing) four rows at present; and TestResults. You could use a Form based on
the patients table with a Subform based on the Results table; on the subform
you would navigate to the new record, select the test from a combo box based
on the Tests table, and enter the results. You could then go to the next row
and select a different test from the combo box.

I placed "Final" check box , just to save the final results in Master table .
One record per patient.
My transaction will have atleast 4 records per patient.

Is your "Master" table an attempt to store all of the data - patient
demographics, test specifics, test results, etc. - all in one table? If so
that is the root of the problem; it's neither necessary nor appropriate in a
relational database. What is the structure of your Master table?

I just have to dump all the 70 values from the FORM into transaction table
every time when a change is made and just once in Master table. Is there a
easy solution with out coding?

Proper normalization... which may require some rethinking of your table
structures!

Could you post the names and structure (fieldnames, datatypes) of your tables?

 

[mls via AccessMonster.com]

I am developing a basic form for lab tech to enter the data. To make their
job easier I am showing all the demographic data on the form so that it will
be easy for them to identify the patient. I have a field call Test_No which
is a drop down with Test1- Test4 values. I have a FIND button if lab tech
need to enter values for patient1 then all the previous test information will
show up on the form , lab tech have to select the TestNo and enter the new
values for the same patient, and this should save as seperate row in the
table.
I don't need any normalization here. I am getting the demographic data from 4
different tables and will have only 1500 records at the most. I am sending
few of my fields..
# Variable Type Len
1 LAB_ID__ Num 8
2 Date_Received Num 8 DATETIME19. DATETIME19.
9 Login_Date Num 8
10 User_ID Char 50
13 Date_Coll_t Num 8 DATETIME19. DATETIME19.
14 Patient_Age Char 7
15 Patient_Sex Char 3
16 Patient_Location Char 50
17 Extent_Activity Char 12
18 Passage_History Char 13
19 _Sub_Type Char 20
21 Seq_ Char 5
22 Test_No Char 10
23 Isolate_Test Char 5
24 Volume_Tested Char 20
25 Dx Num 8
26 A Num 8
27 A_TESTED_ Num 8
28 A_CT_Value Num 8
29 B Num 8
30 B_TESTED_ Num 8
31 B_CT_Value Num 8
65 RP Num 8
66 RP Num 8
67 RP_CT_Value Num 8
68 Run_File_Name Char 255
69 Operator Char 10
70 Date_tested Num 8 DATETIME19. DATETIME19. Date
tested
71 Date_reported Num 8 DATETIME19. DATETIME19. Date
reported
72 Result_reported Char 100
73 NOTES Char 255
74 Final_Test Num 8

I heard access doesn't follow any relational database rules. It's more
relaxed, one cannot lock the databse if other person is updating it etc..

Thanks again.

 

[John W. Vinson]

I heard access doesn't follow any relational database rules. It's more
relaxed, one cannot lock the databse if other person is updating it etc..

You heard some of the very commonly propagated FALSEHOODS about Access.

It does follow relational database rules, it does enforce referential
integrity, it gives flexible control over table, block or record locking.

And your table is not correctly normalized, and the fact that it isn't
correctly normalized is at the root of the problems that you're having.

 

[mls via AccessMonster.com]

John, what can I do to come out of this mess?
One simple question:Is there a way to lock the field apart from control
property locked=Yes. because people can easily go to the design view of the
from and change the locked=No and edit the field, becuase I am linking my
tables to real time data which gets updated frequently.

Also is there a way to read specific columns and rows (cells) from excel
spreadsheet in ACCESS.

Thanks for all your answers.

 

[John W. Vinson]

John, what can I do to come out of this mess?

Well, can't say that it will be all that easy, but I would CERTAINLY start
with normalizing your data. Rather than one huge Master table (note: 30 fields
is a VERY wide table), with repeating values for Patient_Age and other patient
data, and multiple fields for test results, you should have a Patients table
with that information STORED ONCE ONLY. You would use a Form/Subform
arrangement to *display* the patient data (on the mainform), and *enter* the
test results data (on a subform).

One simple question:Is there a way to lock the field apart from control
property locked=Yes. because people can easily go to the design view of the
from and change the locked=No and edit the field, becuase I am linking my
tables to real time data which gets updated frequently.

Users should certainly NOT have any ability to go into design view on a
form.... <SHUDDER>.

Give them a .mde file and a runtime installation of Access, or implement
Access Workgroupg Security if need be... but that question should simply never
come up. Nor should the user ever have any need to get around your security
and edit the field; on a properly normalized database with properly designed
forms, they can easily enter or edit anything they should be able to edit, and
have no way to edit anything they shouldn't.

Also is there a way to read specific columns and rows (cells) from excel
spreadsheet in ACCESS.

Take a look at the VBA help for "TransferSpreadsheet". It's easy to import
named ranges into a table; you can also link to a spreadsheet and run
selective Append or Update queries to migrate data from Excel into your
tables.

 

[mls via AccessMonster.com]

Thanks a lot John.

Well, can't say that it will be all that easy, but I would CERTAINLY start
with normalizing your data. Rather than one huge Master table (note: 30 fields
is a VERY wide table), with repeating values for Patient_Age and other patient
data, and multiple fields for test results, you should have a Patients table
with that information STORED ONCE ONLY. You would use a Form/Subform
arrangement to *display* the patient data (on the mainform), and *enter* the
test results data (on a subform).


Users should certainly NOT have any ability to go into design view on a
form.... <SHUDDER>.

Give them a .mde file and a runtime installation of Access, or implement
Access Workgroupg Security if need be... but that question should simply never
come up. Nor should the user ever have any need to get around your security
and edit the field; on a properly normalized database with properly designed
forms, they can easily enter or edit anything they should be able to edit, and
have no way to edit anything they shouldn't.


Take a look at the VBA help for "TransferSpreadsheet". It's easy to import
named ranges into a table; you can also link to a spreadsheet and run
selective Append or Update queries to migrate data from Excel into your
tables.