Unable to insert values even when primary keys are not null

[Guest]

Hi all

I've been stucked at this for sometime. But nothing i do seems to solve my problem

I have a form that allow user to enter the daily schedule of the bus driver to pick up the students. A query, qryBusSchedule, retrieving data from 2 tables, is used as the recordsource of the form. The 2 tables are studentMaster table and the transportRecord table. These 2 tables are related based on the studentNo

Structure of the tables and queries are as follow
studentMaster(*studentNo, Name, Surname, ....
transportRecord(*Date, *Time, +studentNo, fromLocation, toLocation
qryBusSchedule(transportRecord.Date, transportRecord.Time, studentMaster.Name, transportRecord.studentNo, transportRecord.fromLocation, transportRecord.toLocation

* symbolises P
+ symbolises F
transportRecord's primary key is a composite key of Date, Time and studentNo(foreign key from studentMaster table

SQL codes for generating the query, qryBusSchedule
-----
SELECT studentMaster.Name, transportRecord.
FROM studentMaster, transportRecor
WHERE (((transportRecord.IDNo)=studentMaster.IDNo)
ORDER BY transportRecord.Date, transportRecord.Time
-----
In the form, Name is a combo box, when the user select the name of the student, the studentNo textfield is automatically populated.

Problem:
Even though I've entered values for Date, Time and selected a value for Name (studentNo, automatically populated in studentNo textfield) and i try to save the new record, I am given an error "Index or Primary key cannot contain a null value". Therefore I am unable to save the item into the bus schedule. BUT if i simply enter the Date, Time and studentNo into their respective textboxes in the form without selecting a value in the combo box for Name, I am able to save the newly inserted record

Can anyone advise me what have I done wrong? and how can I correct my mistake? so that i can allow user to simply select the student's Name instead of the studentNo when adding a new record to the bus schedule

Thanks a lot

yann

 

[Roger Carlson]

If you create a combo box to select the student's name, have the row source
return 2 columns, StudentNo and Name. Set the Number of Columns to 2 and
set the widths to 0"; 1". Now bind the combo to the first field
(StudentNo). Now when you select the student number, the name will be
displayed, but the number will be saved.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi all,

I've been stucked at this for sometime. But nothing i do seems to solve my problem.

I have a form that allow user to enter the daily schedule of the bus

driver to pick up the students. A query, qryBusSchedule, retrieving data
from 2 tables, is used as the recordsource of the form. The 2 tables are
studentMaster table and the transportRecord table. These 2 tables are
related based on the studentNo.

Structure of the tables and queries are as follows
studentMaster(*studentNo, Name, Surname, ....)
transportRecord(*Date, *Time, +studentNo, fromLocation, toLocation)
qryBusSchedule(transportRecord.Date, transportRecord.Time,

studentMaster.Name, transportRecord.studentNo, transportRecord.fromLocation,
transportRecord.toLocation)

* symbolises PK
+ symbolises FK
transportRecord's primary key is a composite key of Date, Time and

studentNo(foreign key from studentMaster table)

SQL codes for generating the query, qryBusSchedule.
------
SELECT studentMaster.Name, transportRecord.*
FROM studentMaster, transportRecord
WHERE (((transportRecord.IDNo)=studentMaster.IDNo))
ORDER BY transportRecord.Date, transportRecord.Time;

student, the studentNo textfield is automatically populated.

Problem:
Even though I've entered values for Date, Time and selected a value for

Name (studentNo, automatically populated in studentNo textfield) and i try
to save the new record, I am given an error "Index or Primary key cannot
contain a null value". Therefore I am unable to save the item into the bus
schedule. BUT if i simply enter the Date, Time and studentNo into their
respective textboxes in the form without selecting a value in the combo box
for Name, I am able to save the newly inserted record.

Can anyone advise me what have I done wrong? and how can I correct my

mistake? so that i can allow user to simply select the student's Name
instead of the studentNo when adding a new record to the bus schedule.

 

[Guest]

Hi Roger

Thanks for reply, I've tried as you advised, but I am still getting the "Index or Primary key cannot contain a null value" error when I try to save the new bus schedule I've entered. Also if I set the boundColumn to be the StudentNo, Even though I have specify the Control Source of the combo box to be the studentName, no values was displayed in the combo box for existing records for bus schedules

Do you have any idea why I am still encountering that error?! thanks
yan
----- Roger Carlson wrote: ----

If you create a combo box to select the student's name, have the row sourc
return 2 columns, StudentNo and Name. Set the Number of Columns to 2 an
set the widths to 0"; 1". Now bind the combo to the first fiel
(StudentNo). Now when you select the student number, the name will b
displayed, but the number will be saved

--
--Roger Carlso
www.rogersaccesslibrary.co
Reply to: Roger dot Carlson at Spectrum-Health dot Or

Hi all

driver to pick up the students. A query, qryBusSchedule, retrieving dat
from 2 tables, is used as the recordsource of the form. The 2 tables ar
studentMaster table and the transportRecord table. These 2 tables ar
related based on the studentNo

studentMaster(*studentNo, Name, Surname, ....
transportRecord(*Date, *Time, +studentNo, fromLocation, toLocation
qryBusSchedule(transportRecord.Date, transportRecord.Time

studentMaster.Name, transportRecord.studentNo, transportRecord.fromLocation
transportRecord.toLocation

+ symbolises F
transportRecord's primary key is a composite key of Date, Time an

studentNo(foreign key from studentMaster table

-----
SELECT studentMaster.Name, transportRecord.
FROM studentMaster, transportRecor
WHERE (((transportRecord.IDNo)=studentMaster.IDNo)
ORDER BY transportRecord.Date, transportRecord.Time

student, the studentNo textfield is automatically populated

Even though I've entered values for Date, Time and selected a value fo

Name (studentNo, automatically populated in studentNo textfield) and i tr
to save the new record, I am given an error "Index or Primary key canno
contain a null value". Therefore I am unable to save the item into the bu
schedule. BUT if i simply enter the Date, Time and studentNo into thei
respective textboxes in the form without selecting a value in the combo bo
for Name, I am able to save the newly inserted recordmistake? so that i can allow user to simply select the student's Nam
instead of the studentNo when adding a new record to the bus schedule

 

[Guest]

Hi Roger,

I realised where's my mistake, I should have set the control source of my combo box to be the studentNo instead of the studentName. Now my records are saving properly. Thanks
yann