Add Query Based on if Condition

[shakeelmscw]

I have a form Name " CompanyEnterDB " where i have create Add button, which run a query to add some data in students database table name " CompanyStudents "



On CompanyEnterDB form i have created 5 unbound text boxes 1st: Ttl 2nd: Lname 3rd: Fname 4th: Gt 5th: Job_Number

Now i have created one add Query named CompanyaddStudent

So when open form CompanyEnterDB Fill The 5 boxes and click on add button, this is working perfectly and adding the data.

But my problem is this, if i press add button again, it add the same record again. i need your help to program like this if any of text box is empty, add button should not work and give message that we need to fill the unbound boxes 1st and if we after filling all the boxes we press add button two time. it should only add record once and 2nd time it should give message that same record already exist.

[June7]

Why do you need Add button (show the code)? Data entered/edited on a bound form is committed to table when form closes, move to another record, or run code (Save, not Add).

[shakeelmscw]

Why do you need Add button (show the code)? Data entered/edited on a bound form is committed to table when form closes, move to another record, or run code (Save, not Add).

I have done like this. CompanyEnterDB is a form, where i attached subform for CompanyStudents in datasheet view. so when someone click on Add button the data from form added to CompanyStudents table and that datasheet view table show all records associated with same job number on CompanyEnterDB subform.

INSERT INTO CompanyStudents ( [First Name], [Last Name], Gender, Title, Job_Number )
SELECT [Forms]![CompanyEnterDB]![FName] AS FN, [Forms]![CompanyEnterDB]![Lname] AS LN, [Forms]![CompanyEnterDB]![Gt] AS Gend, [Forms]![CompanyEnterDB]![ttl] AS TTL1, [Forms]![CompanyEnterDB]![Job_Number] AS JN
FROM CompanyStudents;

Now i have already added the if condition which checks the unbound boxes, If anyone of box is empty it is asking me to fill the box first. i have done this through macros.

so now i only need a code to verify that if exactly same record exit in target table, it should give message that same record already exist.

[June7]

If the form is bound then running an INSERT sql will result in two records. As stated, record is committed to table when close form, move to another record, run Save code.

[shakeelmscw]

Yes you are right. My CompanyEnterDB Form is Bound to CompanyEnterDB Table. I want to use current job number while i click add record button. i need the form to add only one record to CompanyStudents table while i should be able to navigate in the same form. Now i am facing the same problem as you said. Can you guide me what is the solution or a better way to do that.

[June7]

Wait, I assumed you were INSERTing record into same table the form is bound to, which would result in 2 records.

If forms are set up properly, sql INSERT actions should not be needed. Maybe a form/subform arrangement with CompanyEnterDB and CompanyStudents is called for.

Since I don't know your data and relationships, hard to give specific advice.

[shakeelmscw]

Thanks for your reply. i try to explain why i am using add button.

I have a form Name " CompanyEnterDB " which is based on " CompanyEnterDB " table. where i have created one "Add button". I also have a subform in the " CompanyEnterDB " form. which is only showing all records matching [Job_number] field in "CompanyEnterDB" (currently opened form) and subform (i have same [job_number] field in subform).

Add button runs a query to add data in one table name " CompanyStudents " (subform is based on this table)


add query coding
INSERT INTO CompanyStudents ( [First Name], [Last Name], Gender, Title, Job_Number )
SELECT [Forms]![CompanyEnterDB]![FName] AS FN, [Forms]![CompanyEnterDB]![Lname] AS LN, [Forms]![CompanyEnterDB]![Gt] AS Gend, [Forms]![CompanyEnterDB]![ttl] AS TTL1, [Forms]![CompanyEnterDB]![Job_Number] AS JN
FROM CompanyStudents;



On CompanyEnterDB form i have created 4 unbound text boxes 1st: Ttl 2nd: Lname 3rd: Fname 4th: Gt
some bound text boxes: Job_Number, ID etc
Subform contain bound boxes. firstname, lastname. Job_number etc

I know if i want to enter data in main form or subform i can simply type the data and data will be saved to connected table. but my requirement is that i want job_number given in main form should be automatically added in the subform job_number field of each new record. so person entering data should be able to see how many students he have added under that job_number.

Presently What method i used i am facing this problem. 1st when i click on Add button it add multiple records with same data as you said due to opened bound datable (does this rule apply on subform?) I need whenever i click add button it should add one record at a time.

[June7]

That INSERT query you show will create new records for as many records as there are currently in CompanyStudents but they will all have the same data from the main form entries. Do you want only one new record inserted?

If the Master/Child links properties of the subform container control are set to the job_number and job_number defines the pk/fk relationship of these two tables, then the job number from main form will automatically save to the new record in subform. If job_number is not the pk/fk then there are better ways than INSERT sql to populate the job number field in subform.

Do you have a table of Students?

[shakeelmscw]

That INSERT query you show will create new records for as many records as there are currently in CompanyStudents but they will all have the same data from the main form entries. Do you want only one new record inserted?

If the Master/Child links properties of the subform container control are set to the job_number and job_number defines the pk/fk relationship of these two tables, then the job number from main form will automatically save to the new record in subform. If job_number is not the pk/fk then there are better ways than INSERT sql to populate the job number field in subform.

Do you have a table of Students?

That INSERT query you show will create new records for as many records as there are currently in CompanyStudents but they will all have the same data from the main form entries. Do you want only one new record inserted?

Yes

If the Master/Child links properties of the subform container control are set to the job_number and job_number defines the pk/fk relationship of these two tables, then the job number from main form will automatically save to the new record in subform.

No i did not linked these from master/child ( subform is based on query where i put condition to show only records with forms!CompanyStudentDB!job_number . here are the codes for subform query

SELECT DCount("Gender","CompanyStudents","Gender <='" & [Gender] & "'") AS Sequence, CompanyStudents.[Last Name], CompanyStudents.[First Name], CompanyStudents.Title, CompanyStudents.Gender, CompanyStudents.Job_Number, CompanyStudents.CompanyFROM CompanyStudents
WHERE (((CompanyStudents.Job_Number)=[forms]![CompanyEnterDB]![Job_Number]));

If job_number is not the pk/fk then there are better ways than INSERT sql to populate the job number field in subform.

>> i don't know about pk/fk ?? what is this? can you explain master/child pk/fk method?


<< Do you have a table of Students? >> " CompanyStudents " is a table where i am storing all the students.

[June7]

pk/fk means primary key/foreign key

Review http://office.microsoft.com/en-us/ac...010098674.aspx

What is the data structure? Are students unique in CompanyStudents (is there only one record per student)?

To INSERT only one record don't use SELECT in this case because all the data for insertion is on the form:

INSERT INTO CompanyStudents ([First Name], [Last Name], Gender, Title, Job_Number )
VALUES([Forms]![CompanyEnterDB]![FName], [Forms]![CompanyEnterDB]![Lname], [Forms]![CompanyEnterDB]![Gt], [Forms]![CompanyEnterDB]![ttl], [Forms]![CompanyEnterDB]![Job_Number]);

You will have to refresh the form afterward for the new record to display.

[shakeelmscw]

pk/fk means primary key/foreign key

Review http://office.microsoft.com/en-us/ac...010098674.aspx

What is the data structure? Are students unique in CompanyStudents (is there only one record per student)?

To INSERT only one record don't use SELECT in this case because all the data for insertion is on the form:

INSERT INTO CompanyStudents ([First Name], [Last Name], Gender, Title, Job_Number )
VALUES([Forms]![CompanyEnterDB]![FName], [Forms]![CompanyEnterDB]![Lname], [Forms]![CompanyEnterDB]![Gt], [Forms]![CompanyEnterDB]![ttl], [Forms]![CompanyEnterDB]![Job_Number]);

You will have to refresh the form afterward for the new record to display.

Thanks it is working perfectly.

Now only problem is that my sequence is not giving correct value. infect i need to show serial number 1,2,3... (to show how many students have Job_number 1 assigned. can you help me to correct this also.

[June7]

I wouldn't store this value in table. Counting how many students assigned to job can be calculated. Assigning a student 'sequence' id to these records seems irrelevant and unnecessary as well as problemmatic.

[shakeelmscw]

no i did not assigned sequence in table, if you check code for the sequence. but these are giving wrong serial number now. Below are the codes i used in subform

SELECT DCount("Gender","CompanyStudents","Gender <='" & [Gender] & "'") AS Sequence, CompanyStudents.[Last Name], CompanyStudents.[First Name], CompanyStudents.Title, CompanyStudents.Gender, CompanyStudents.Job_Number, CompanyStudents.CompanyFROM CompanyStudents
WHERE (((CompanyStudents.Job_Number)=[forms]![CompanyEnterDB]![Job_Number]));

[June7]

Can't create a 'sequence' value like that. Review http://www.lebans.com/rownumber.htm

Textbox in report has a RunningSum property. This can be used to generate sequential number for each company group but the sequence number is not saved in a table, it lives only on the report.

[shakeelmscw]

Can't create a 'sequence' value like that. Review http://www.lebans.com/rownumber.htm

Textbox in report has a RunningSum property. This can be used to generate sequential number for each company group but the sequence number is not saved in a table, it lives only on the report.

As i already said, i am looking for a way to create a serial number live only. just to know how many students have been added. please give me any procedure to add serial number with query.