how to write code in VBA to insert data to table from form?
Thank you for taking the time to report an issue.
What's wrong... Please write below.
The table opens but only the first record is updated. The code works well as a standard VBA search code on another form.
However, the problem I am facing is that when I try to edit the form (design view) or close the form, it gives an error:
ODBC --call failed
Cannot Insert the value NULL into the column. Column does not allow nulls. INSERT fails. The statement has been terminated.
When this error starts to appear, I am unable to view the form in design view or save it. But I am able to make entries to the table.
I am using this VBA code for the 'Add Record' button:
Table Name: tblAwardsLog
4 fields: LastName , FirstName , Recommended , DateInitiated
All fields are TEXT fields. even DateInitiated
I have this Awards Log almost to where I want it, but just can't seem to get this one thing to work right.
I guess I can read this using some VBA code posted at | [LINK] and | [LINK], add an Instr function to it so that when I read the text file, I know when to | insert the data properly in the table.
1. Is there another way I can directly import the data into an Access 2007 table without VBA code? I know Access allows import from data, but my data is not as formatted as I want so the data might end up being in the table in a way such as:
I want to know in which control user edit data and I want to insert only edited controls data in other table. how can I write insert query
This works fine where all the fields are text boxes (I.e. not combos or list boxes) but the problem I'm having is that the parent table "tblContracts" has 4 lookup tables (which are used for combos with various fields on the form) and these are numeric fields which link between the tables (I.e. In the parent table the field would be SupplierID whick links to the child table SupplierID field) In the form obviously it displays the actual name rather than the index numerical value when the user clicks the dropdown menu. The issue (I think?) is that the VBA code I have is trying to insert a numerical value (The ID field value) rather than the text which is displayed in the dropdown menu. How can I get round this?
Patient table has ( MRN, Name ) Where MRN is Primary Key.
Scheduling table has ( ID, MRN, StudyID ) WHERE ID ( Autonumber) is primary key.
Tracking table has ( ID, MRN, StudyID ) WHERE ID ( Autonumber) is primary key.
The relationship between Patient table and the Scheduling table is one to many relationship.Similarly, the relationship between Patient table and the Tracking is table is one to many relationship. Because there can be any number of health studies for a patient.
The form Scheduling shows the MRN and StudyID and when I hit save the MRN and STUDYID saves in the Scheduling table but the same data should be inserted into Tracking table if the same combination does not exist.
For example: Scheduling table has the following data:
and so when I open the form it will show MRN as 1234 and StudyID as 2 or 1 depending on my previous form selection of the study ID.
Assume if initially the Tracking table has only following data: MRN STUDYID 1234 1
and if it doesn't have
Then when I save the form scheduling with MRN = 1234 and Studyid = 2
The Tracking table should also automatically have the following data: MRN STUDYID
Basically I like to write a VBA code behind the form Scheduling where it should save the data in the table Tracking if the data ( MRN and STUDYID) does not exist.
So far I can create the forms and combo boxes but the selected items are not written to the main table.
Do I really need vba to write the data from combo boxes to a table from a form or am I missing something.
As of right now I have a main form with 2 buttons, one for the search and one for the add. The form for the add is finished except for the VBA in the back end. The fields in the add form correspond to the fields in the table but I can't figure out how to go about getting new records into the table.
Is it an SQL INSERT statement? Or is there a built in function in VBA thatwill allow me to do this?