Not able to fetch the records in tabbed form

[mohamed aqeel]

Hi friends,

I am developing a database for a small organisation.

I linked 6 tables(basic info,educational info,employment info,passport info,leave info,personal info) in one tabbed form.For all the tables i have used emp id as the primary key.(for data entry).

I have created a similar form for data modification also.

The problem is i am able to view only the records for which i have entered all the information(i.e atleast one entry in a table). As i already mentioned in the tabbed form if i didnt specify the values for 1 table (i.e) 1 tab, then i am not able to retrieve the whole record(even if i entered all the values for all the remaining tables(remaining tabs))
please help to solve this problem...this is very essential because I cant enter all the values in the first attempt, so i need to enter the known values first, and only when i get the other details i need to search that record and i hv to update that record..

NOTE:

The records are being properly saved in the table.

for more clarity i will say an example.

For an employee

emp id: 101
name : abcd

I hv entered the basic info tab, (remaining tab if i don't fill up) that table is updated properly.but while searching this record i am not able to retrieve.Below is the code i hv used to search the record...

THANKS IN ADVANCE!!!!

Private Sub Command84_Click()

Dim strStaffRef As String
Dim strSearch As String

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter the Staff Employee Id value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in EMP ID
DoCmd.ShowAllRecords
DoCmd.GoToControl ("EMP ID")
DoCmd.FindRecord Me!txtSearch

[EMP ID].SetFocus
strStaffRef = [EMP ID].Text
txtSearch.SetFocus
strSearch = txtSearch.Text

If strStaffRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
[EMP ID].SetFocus
txtSearch = ""
'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", , "Invalid Search Criterion!"
txtSearch.SetFocus

End If

End Sub

 

[Mr. B]

It is not clear from you post but have you created a query in which you have linked all of these tables and then used that query as the record source for your form?

This is a situation where you need a form and sub-forms to interact with the data.

If I am understanding your data structure correctly, you would need to have the primary table (the one with the primary key of "EMP_ID") or a query based on that primary table and use that as the record source for your main form.

You would then create a form for each of the other tables that need to link to the primary table and then use each of these forms as sub-forms in you main form.

You would then use the Link Master Fields and Link Child Fields properties of each sub-form to link the data together.

 

[mohamed aqeel]

Select [staff info1].[sl no], [staff info1].[emp id], [staff info1].[emp fname], [staff info1].[emp lname], [staff info1].[emp photograph], [staff info1].[emp grade], [staff info1].[emp sex], [staff info1].[emp e-mail id], [staff info1].[saudi contact address], [staff info1].city, [staff info1].state, [staff info1].[saudi contact number], [emp educational info].[academic qualification], [emp educational info].[professional qualification], [emp educational info].[university name], [emp educational info].[authenticated?], [emp educational info].certifications, [emp employment info].[doj in iis jubail], [emp employment info].[do confirmation in iis jubail], [emp employment info].[total experience in iis jubail], [emp employment info].[gross pay at the time of joining (in sr)], [emp employment info].[gross pay at present (in sr)], [emp leave details].[leave taken account during school working days], [emp leave details].[total no of days on loss of pay], [emp visa,passport & iquama info].[passport no], [emp visa,passport & iquama info].[passport issue date], [emp visa,passport & iquama info].[passport expiry date], [emp visa,passport & iquama info].[iqama no], [emp visa,passport & iquama info].[iqama issue date], [emp visa,passport & iquama info].[iqama expiry date], [emp personal info].dob, [emp personal info].[marital status], [emp personal info].[spouse name], [emp personal info].[no of kids], [emp personal info].[india address], [emp personal info].city as [city_emp personal info], [emp personal info].state as [state_emp personal info], [emp personal info].[zip code], [emp personal info].[india contact no], [emp visa,passport & iquama info].[visa type]
from [emp personal info] inner join ([emp visa,passport & iquama info] inner join ([emp leave details] inner join ([emp employment info] inner join ([emp educational info] inner join [staff info1] on [emp educational info].[emp id] = [staff info1].[emp id]) on [emp employment info].[emp id] = [emp educational info].[emp id]) on [emp leave details].empid = [emp educational info].[emp id]) on [emp visa,passport & iquama info].[emp id] = [emp educational info].[emp id]) on [emp personal info].[emp id] = [emp educational info].[emp id];

 

[mohamed aqeel]

Hi Mr.B,

I am uploading my database for more clarity.


Looking forward to ur reply,

Thanks in advanvce!!

 

[carl024]

you need
Daily enter data form
Find record form
You attach file again this mdb not open

 

[Mr. B]

From the looks of the sql statement that you posted, you should be able to view the data returned by this SELECT statement but you would most likely not be able to add new records or mange the data as you are needing to do. I suspect that one or more of the tables other than the "staff info1" table are there to provide, at a minimum, a one-to-many relationship. If this is true, that is exactly why I indicated that you need the use a Form with multiple Subforms to manage your data.

 

[mohamed aqeel]

Hi All,

Thank u soo much!!!

I hv read some articles regarding access table relationship and I modified it accordingly,and now my db is working in a desirable manner.

Mr.B, Thank u soo much for ur correct guidance towards this..

THANKS EVERYONE!!!