find record in main form then find record in the subform

[mhmaid]

Hi all
I have this code which will find the record on the main form.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

If IsLoaded("Invoices Register") Then
If Forms![INVOICEs REGISTER]![Invoice
details1].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "CpvId"
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![CpvId]
End If
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub




what if also i want it to find the record on the subform.
like this
me.cpvsubform.setfocus
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

I want the code after it finds the cpvid , to go to the record in the
subform which is the same in Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

i have tried to add this to the code but it didnt work ,if i keep only one
of them , code will work ( if i want to find only record of subform , or only
record of main form)

thanks

 

[Jeff Boyce]

A standard mainform/subform construction has the subform automatically
finding the related records when the mainform's record changes. Check the
properties of your subform control to see if you have the "parent" and
"child" fields set properly.

NOTE: this approach presumes you have well-normalized table structure.
"How" depends on "what" ... i.e., what data structure you have. You haven't
described your data/table structure...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[mhmaid]

thank your for reply

my db is just a developed copy of the sample db "expense reports" which
comes with the windows office .the table "employees " has been renamed to
"Patients" and added /deleted many other fiels to match our needs.


first of all , instead of the two tables called "expense reports ","expense
details"
I have four tables ,which are these two plus another two tables
,"cashPaymentvouchers",Invoice


each table has its primary key
1.CashPaymentVouchers---CpvId
2.Invoices---InvoiceId
3.Expense Reports---ExpenseReportId
4.Expense Details---ExpensedetailId


the reason for adding the other two table is the following

my job is receiving , checking and accounting Invoices that belongs to
Patients treatment,Patients tickets ,etc.

our system is to add a record in the main table "Patients" for each type we
send the patient abroad for treatment.the primary key for this table is Caseid

we are sending patients to more than 20 countries , and in some of these
countries we receive the invoice directly , others we receive them thru our
embassies in those countries

in the cases of invoices received thru our embassies , the embassy we pay
the bill ,we attach bill, draft copy or chq copy to a cash payment voucher
(CPV)
they will send the bill fortnightly , along with a covering letter , lets
call it a batch , so one batch will contain many cpv's , one cpv may contain
many invoices , one invoice may belongs to more than one case



in order to enter each cpv one time only , and to enter each invoice one
time only , I have added these two table "cashpaymentvouchers","invoices"


apart from this , I have a fifth table which is called "Invoice Register"
this table is used to enter the data for each batch like batch number ,
country, batch amount , currency ,date received , date sent for payment.

when we arrange to send patient abroad , at this point I will add a record
to the table "Patients" which off course will include Travel date , country
of treatment , Hospital etc.

some of the expenses like advance allowances given to patient ,will be
accounted before the travel of the patient, these will not be entered in the
Invoice Register, as there are no invoice here , but there are cpv , so , I
will first till you what i do in this case , then I will till you how I enter
the Invoice details

we have arranged to sent a patient abroad
1.I will open the form"Expense reports by patient" sourceatients
table,this form got a subform "Patients subform", which will show the list of
expenses for the patient.(the source of this subform is a union query for the
four tables:cpv,invoice,Expense report,Expense details)

here I have a button when clicked will open another form
"Cashpaymentvouchers", with its subform"cpv subform" .source for the first is
table cpv , souce of the 2nd is table "invoices.

my question was about this stage here , the form "CashPaymentVouchers"
source is this :SELECT DISTINCTROW [CashPaymentVouchers].* FROM
CashPaymentVouchers WHERE (([CashPaymentVouchers].Batchid=forms![Invoices
Register]!Batchid)) order by cpvid;

the code which I gave is in the on activate event of this form,when i open ,
i want it to go to the same cpv which was selected thru the subform"patients
subform", and ALSO, to go to the same InvoiceId that was selected there, off
course it cannot go directly to the invoiceid , first we have to select the
cpv that this invoiceid is in.

so, now when i open the cashpaymentvouchers form , it will take me to the
correct cpv , but as you came to know that ,one cpv may contain many invoices
, that why i want to also find the invoice which was selected thru the
patients subform.

now if i receive invoices , first I will open the form invoice register
,then thru this i have another 4 forms simillary to the above forms , invoics
subform will show the list of invoices for the current batch.

hope i made it clear , I hope someone can help

A standard mainform/subform construction has the subform automatically
finding the related records when the mainform's record changes. Check the
properties of your subform control to see if you have the "parent" and
"child" fields set properly.

NOTE: this approach presumes you have well-normalized table structure.
"How" depends on "what" ... i.e., what data structure you have. You haven't
described your data/table structure...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi all
I have this code which will find the record on the main form.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

If IsLoaded("Invoices Register") Then
If Forms![INVOICEs REGISTER]![Invoice
details1].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "CpvId"
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![CpvId]
End If
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub




what if also i want it to find the record on the subform.
like this
me.cpvsubform.setfocus
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

I want the code after it finds the cpvid , to go to the record in the
subform which is the same in Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

i have tried to add this to the code but it didnt work ,if i keep only one
of them , code will work ( if i want to find only record of subform , or only
record of main form)

thanks

 

[Jeff Boyce]

I may have misunderstood your description...

It sounds like you have tables with the same (identical) structure, but used
to hold different "types" of (?)invoicing.

If this is your situation, your database is a spreadsheet! In a relational
database like Access, you'd use a single table with the fields, then add one
more to indicate "type".

I'm not confident yet that I understand your table structure, and in Access,
it all starts with the data. "How" depends on "what".

Could you post back a simple table structure description? For example, a
very rudimentary student enrollment data structure might look like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thank your for reply

my db is just a developed copy of the sample db "expense reports" which
comes with the windows office .the table "employees " has been renamed to
"Patients" and added /deleted many other fiels to match our needs.


first of all , instead of the two tables called "expense reports ","expense
details"
I have four tables ,which are these two plus another two tables
,"cashPaymentvouchers",Invoice


each table has its primary key
1.CashPaymentVouchers---CpvId
2.Invoices---InvoiceId
3.Expense Reports---ExpenseReportId
4.Expense Details---ExpensedetailId


the reason for adding the other two table is the following

my job is receiving , checking and accounting Invoices that belongs to
Patients treatment,Patients tickets ,etc.

our system is to add a record in the main table "Patients" for each type we
send the patient abroad for treatment.the primary key for this table is Caseid

we are sending patients to more than 20 countries , and in some of these
countries we receive the invoice directly , others we receive them thru our
embassies in those countries

in the cases of invoices received thru our embassies , the embassy we pay
the bill ,we attach bill, draft copy or chq copy to a cash payment voucher
(CPV)
they will send the bill fortnightly , along with a covering letter , lets
call it a batch , so one batch will contain many cpv's , one cpv may contain
many invoices , one invoice may belongs to more than one case



in order to enter each cpv one time only , and to enter each invoice one
time only , I have added these two table "cashpaymentvouchers","invoices"


apart from this , I have a fifth table which is called "Invoice Register"
this table is used to enter the data for each batch like batch number ,
country, batch amount , currency ,date received , date sent for payment.

when we arrange to send patient abroad , at this point I will add a record
to the table "Patients" which off course will include Travel date , country
of treatment , Hospital etc.

some of the expenses like advance allowances given to patient ,will be
accounted before the travel of the patient, these will not be entered in the
Invoice Register, as there are no invoice here , but there are cpv , so , I
will first till you what i do in this case , then I will till you how I enter
the Invoice details

we have arranged to sent a patient abroad
1.I will open the form"Expense reports by patient" sourceatients
table,this form got a subform "Patients subform", which will show the list of
expenses for the patient.(the source of this subform is a union query for the
four tables:cpv,invoice,Expense report,Expense details)

here I have a button when clicked will open another form
"Cashpaymentvouchers", with its subform"cpv subform" .source for the first is
table cpv , souce of the 2nd is table "invoices.

my question was about this stage here , the form "CashPaymentVouchers"
source is this :SELECT DISTINCTROW [CashPaymentVouchers].* FROM
CashPaymentVouchers WHERE (([CashPaymentVouchers].Batchid=forms![Invoices
Register]!Batchid)) order by cpvid;

the code which I gave is in the on activate event of this form,when i open ,
i want it to go to the same cpv which was selected thru the subform"patients
subform", and ALSO, to go to the same InvoiceId that was selected there, off
course it cannot go directly to the invoiceid , first we have to select the
cpv that this invoiceid is in.

so, now when i open the cashpaymentvouchers form , it will take me to the
correct cpv , but as you came to know that ,one cpv may contain many invoices
, that why i want to also find the invoice which was selected thru the
patients subform.

now if i receive invoices , first I will open the form invoice register
,then thru this i have another 4 forms simillary to the above forms , invoics
subform will show the list of invoices for the current batch.

hope i made it clear , I hope someone can help

A standard mainform/subform construction has the subform automatically
finding the related records when the mainform's record changes. Check the
properties of your subform control to see if you have the "parent" and
"child" fields set properly.

NOTE: this approach presumes you have well-normalized table structure.
"How" depends on "what" ... i.e., what data structure you have. You haven't
described your data/table structure...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi all
I have this code which will find the record on the main form.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

If IsLoaded("Invoices Register") Then
If Forms![INVOICEs REGISTER]![Invoice
details1].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "CpvId"
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![CpvId]
End If
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub




what if also i want it to find the record on the subform.
like this
me.cpvsubform.setfocus
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

I want the code after it finds the cpvid , to go to the record in the
subform which is the same in Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

i have tried to add this to the code but it didnt work ,if i keep only one
of them , code will work ( if i want to find only record of subform ,

or
only

record of main form)

thanks

 

[mhmaid]

thanks for response

main table is patients ( it holds all the visits for the patients , may
hold more than one visit for a patient , let us call the visit , a case

Caseid
Patientnameid , this is numeric field , with lookup names from tblpatientnames
hospitalid
countryid
traveldate
returndate
diagnosisCategoryid, e.g.cancer,cardiac etc.
Diagnosisdetailsid .e.g.breast cancer,prostate cancer etc.
TreatingDoctor
CaseType,e.g.sent abroad,reimbursed,referred to a local hospital,etc.

and many other fields , these are the main

then

expense are separated over four tables

1.CashPaymentVouchrs ( cpv)

Cpvid
batchid from table Invoice register
cpvnumber
cpvdate
cpvAmount
Currency
Roe
notes

2.Invoices

Invoiceid
CpvId = from table cashpaymentvouchers
invoicenumber
invoicedate
invoiceIssuer
PatientnameId
invoiceamount

3.Expense reports
expensereportid
Invoiceid,from table invoices
Caseid , from table Patients
ExpRptAmount

4.Expense details
expensedetailid
Expensereportid
ExpenseCategoryid
expenseDescriptionid
expensedatefrom
expensedateto
expenseitemamount
accountingdate

if you see, i have a develped copy of the sample "expense reports" database,
i have renamed most of the fields, and I have added two more table for the
expenses to match with our requirments

when i start using access , with only the last two tables as the sample, I
faced alot of problems, one invoice may be for more than one case.so , i used
to enter the same invoice number two times , with part of invoice amount ,
that is why I added the table invoices.the same thing applies for the table
cpv, as one cpv may hold invoices for many patients.so without have the new
two tables, i used to enter the cpv details directly in the table expense
reports , and because at that stage i have also to enter the case id , so I
have to enter once cpv many times , but each time with part of the amount
that belongs to a case.

the relationship betw the table patients , and expense table is Caseid ,
which is in table 3, expense reports.


I have one more table ,Invoice register , which holds details of all
incoming invoice batchs

batchid
batchnumber
batchdate
countryid
batchamount
currency
dateBatchReleaseforpayment
etc.

when i receive invoices ,first i will enter batch detail in the invoice
register , then enter full details in the 4 expense tables as above.of course
when we send a patient , i will add a new case to the table patients.

i have many other tables like hospital, countries , Doctors etc. to save
space i use number fields in my tables like hospitalid, and lookup the list
from the hospitals ,same to countries etc.

I may have misunderstood your description...

It sounds like you have tables with the same (identical) structure, but used
to hold different "types" of (?)invoicing.

If this is your situation, your database is a spreadsheet! In a relational
database like Access, you'd use a single table with the fields, then add one
more to indicate "type".

I'm not confident yet that I understand your table structure, and in Access,
it all starts with the data. "How" depends on "what".

Could you post back a simple table structure description? For example, a
very rudimentary student enrollment data structure might look like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thank your for reply

my db is just a developed copy of the sample db "expense reports" which
comes with the windows office .the table "employees " has been renamed to
"Patients" and added /deleted many other fiels to match our needs.


first of all , instead of the two tables called "expense reports ","expense
details"
I have four tables ,which are these two plus another two tables
,"cashPaymentvouchers",Invoice


each table has its primary key
1.CashPaymentVouchers---CpvId
2.Invoices---InvoiceId
3.Expense Reports---ExpenseReportId
4.Expense Details---ExpensedetailId


the reason for adding the other two table is the following

my job is receiving , checking and accounting Invoices that belongs to
Patients treatment,Patients tickets ,etc.

our system is to add a record in the main table "Patients" for each type we
send the patient abroad for treatment.the primary key for this table is Caseid

we are sending patients to more than 20 countries , and in some of these
countries we receive the invoice directly , others we receive them thru our
embassies in those countries

in the cases of invoices received thru our embassies , the embassy we pay
the bill ,we attach bill, draft copy or chq copy to a cash payment voucher
(CPV)
they will send the bill fortnightly , along with a covering letter , lets
call it a batch , so one batch will contain many cpv's , one cpv may contain
many invoices , one invoice may belongs to more than one case



in order to enter each cpv one time only , and to enter each invoice one
time only , I have added these two table "cashpaymentvouchers","invoices"


apart from this , I have a fifth table which is called "Invoice Register"
this table is used to enter the data for each batch like batch number ,
country, batch amount , currency ,date received , date sent for payment.

when we arrange to send patient abroad , at this point I will add a record
to the table "Patients" which off course will include Travel date , country
of treatment , Hospital etc.

some of the expenses like advance allowances given to patient ,will be
accounted before the travel of the patient, these will not be entered in the
Invoice Register, as there are no invoice here , but there are cpv , so , I
will first till you what i do in this case , then I will till you how I enter
the Invoice details

we have arranged to sent a patient abroad
1.I will open the form"Expense reports by patient" sourceatients
table,this form got a subform "Patients subform", which will show the list of
expenses for the patient.(the source of this subform is a union query for the
four tables:cpv,invoice,Expense report,Expense details)

here I have a button when clicked will open another form
"Cashpaymentvouchers", with its subform"cpv subform" .source for the first is
table cpv , souce of the 2nd is table "invoices.

my question was about this stage here , the form "CashPaymentVouchers"
source is this :SELECT DISTINCTROW [CashPaymentVouchers].* FROM
CashPaymentVouchers WHERE (([CashPaymentVouchers].Batchid=forms![Invoices
Register]!Batchid)) order by cpvid;

the code which I gave is in the on activate event of this form,when i open ,
i want it to go to the same cpv which was selected thru the subform"patients
subform", and ALSO, to go to the same InvoiceId that was selected there, off
course it cannot go directly to the invoiceid , first we have to select the
cpv that this invoiceid is in.

so, now when i open the cashpaymentvouchers form , it will take me to the
correct cpv , but as you came to know that ,one cpv may contain many invoices
, that why i want to also find the invoice which was selected thru the
patients subform.

now if i receive invoices , first I will open the form invoice register
,then thru this i have another 4 forms simillary to the above forms , invoics
subform will show the list of invoices for the current batch.

hope i made it clear , I hope someone can help

A standard mainform/subform construction has the subform automatically
finding the related records when the mainform's record changes. Check the
properties of your subform control to see if you have the "parent" and
"child" fields set properly.

NOTE: this approach presumes you have well-normalized table structure.
"How" depends on "what" ... i.e., what data structure you have. You haven't
described your data/table structure...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi all
I have this code which will find the record on the main form.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

If IsLoaded("Invoices Register") Then
If Forms![INVOICEs REGISTER]![Invoice
details1].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "CpvId"
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![CpvId]
End If
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub




what if also i want it to find the record on the subform.
like this
me.cpvsubform.setfocus
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

I want the code after it finds the cpvid , to go to the record in the
subform which is the same in Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

i have tried to add this to the code but it didnt work ,if i keep only one
of them , code will work ( if i want to find only record of subform , or
only
record of main form)

thanks

 

[Jeff Boyce]

I'm still having trouble with the concept you describe as "expenses in four
tables". Can you explain this a bit more?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thanks for response

main table is patients ( it holds all the visits for the patients , may
hold more than one visit for a patient , let us call the visit , a case

Caseid
Patientnameid , this is numeric field , with lookup names from tblpatientnames
hospitalid
countryid
traveldate
returndate
diagnosisCategoryid, e.g.cancer,cardiac etc.
Diagnosisdetailsid .e.g.breast cancer,prostate cancer etc.
TreatingDoctor
CaseType,e.g.sent abroad,reimbursed,referred to a local hospital,etc.

and many other fields , these are the main

then

expense are separated over four tables

1.CashPaymentVouchrs ( cpv)

Cpvid
batchid from table Invoice register
cpvnumber
cpvdate
cpvAmount
Currency
Roe
notes

2.Invoices

Invoiceid
CpvId = from table cashpaymentvouchers
invoicenumber
invoicedate
invoiceIssuer
PatientnameId
invoiceamount

3.Expense reports
expensereportid
Invoiceid,from table invoices
Caseid , from table Patients
ExpRptAmount

4.Expense details
expensedetailid
Expensereportid
ExpenseCategoryid
expenseDescriptionid
expensedatefrom
expensedateto
expenseitemamount
accountingdate

if you see, i have a develped copy of the sample "expense reports" database,
i have renamed most of the fields, and I have added two more table for the
expenses to match with our requirments

when i start using access , with only the last two tables as the sample, I
faced alot of problems, one invoice may be for more than one case.so , i used
to enter the same invoice number two times , with part of invoice amount ,
that is why I added the table invoices.the same thing applies for the table
cpv, as one cpv may hold invoices for many patients.so without have the new
two tables, i used to enter the cpv details directly in the table expense
reports , and because at that stage i have also to enter the case id , so I
have to enter once cpv many times , but each time with part of the amount
that belongs to a case.

the relationship betw the table patients , and expense table is Caseid ,
which is in table 3, expense reports.


I have one more table ,Invoice register , which holds details of all
incoming invoice batchs

batchid
batchnumber
batchdate
countryid
batchamount
currency
dateBatchReleaseforpayment
etc.

when i receive invoices ,first i will enter batch detail in the invoice
register , then enter full details in the 4 expense tables as above.of course
when we send a patient , i will add a new case to the table patients.

i have many other tables like hospital, countries , Doctors etc. to save
space i use number fields in my tables like hospitalid, and lookup the list
from the hospitals ,same to countries etc.

I may have misunderstood your description...

It sounds like you have tables with the same (identical) structure, but used
to hold different "types" of (?)invoicing.

If this is your situation, your database is a spreadsheet! In a relational
database like Access, you'd use a single table with the fields, then add one
more to indicate "type".

I'm not confident yet that I understand your table structure, and in Access,
it all starts with the data. "How" depends on "what".

Could you post back a simple table structure description? For example, a
very rudimentary student enrollment data structure might look like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thank your for reply

my db is just a developed copy of the sample db "expense reports" which
comes with the windows office .the table "employees " has been renamed to
"Patients" and added /deleted many other fiels to match our needs.


first of all , instead of the two tables called "expense reports ","expense
details"
I have four tables ,which are these two plus another two tables
,"cashPaymentvouchers",Invoice


each table has its primary key
1.CashPaymentVouchers---CpvId
2.Invoices---InvoiceId
3.Expense Reports---ExpenseReportId
4.Expense Details---ExpensedetailId


the reason for adding the other two table is the following

my job is receiving , checking and accounting Invoices that belongs to
Patients treatment,Patients tickets ,etc.

our system is to add a record in the main table "Patients" for each

type
we

send the patient abroad for treatment.the primary key for this table

is
Caseid

we are sending patients to more than 20 countries , and in some of these
countries we receive the invoice directly , others we receive them

thru
our

embassies in those countries

in the cases of invoices received thru our embassies , the embassy we pay
the bill ,we attach bill, draft copy or chq copy to a cash payment voucher
(CPV)
they will send the bill fortnightly , along with a covering letter , lets
call it a batch , so one batch will contain many cpv's , one cpv may contain
many invoices , one invoice may belongs to more than one case



in order to enter each cpv one time only , and to enter each invoice one
time only , I have added these two table "cashpaymentvouchers","invoices"


apart from this , I have a fifth table which is called "Invoice Register"
this table is used to enter the data for each batch like batch number ,
country, batch amount , currency ,date received , date sent for payment.

when we arrange to send patient abroad , at this point I will add a record
to the table "Patients" which off course will include Travel date , country
of treatment , Hospital etc.

some of the expenses like advance allowances given to patient ,will be
accounted before the travel of the patient, these will not be entered

in
the

Invoice Register, as there are no invoice here , but there are cpv ,

so ,
I

will first till you what i do in this case , then I will till you how

I
enter

the Invoice details

we have arranged to sent a patient abroad
1.I will open the form"Expense reports by patient" sourceatients
table,this form got a subform "Patients subform", which will show the

list
of

expenses for the patient.(the source of this subform is a union query

for
the

four tables:cpv,invoice,Expense report,Expense details)

here I have a button when clicked will open another form
"Cashpaymentvouchers", with its subform"cpv subform" .source for the

first
is

table cpv , souce of the 2nd is table "invoices.

my question was about this stage here , the form "CashPaymentVouchers"
source is this :SELECT DISTINCTROW [CashPaymentVouchers].* FROM
CashPaymentVouchers WHERE (([CashPaymentVouchers].Batchid=forms![Invoices
Register]!Batchid)) order by cpvid;

the code which I gave is in the on activate event of this form,when i

open
,

i want it to go to the same cpv which was selected thru the subform"patients
subform", and ALSO, to go to the same InvoiceId that was selected

there,
off

course it cannot go directly to the invoiceid , first we have to

select
the

cpv that this invoiceid is in.

so, now when i open the cashpaymentvouchers form , it will take me to the
correct cpv , but as you came to know that ,one cpv may contain many invoices
, that why i want to also find the invoice which was selected thru the
patients subform.

now if i receive invoices , first I will open the form invoice register
,then thru this i have another 4 forms simillary to the above forms , invoics
subform will show the list of invoices for the current batch.

hope i made it clear , I hope someone can help



:

A standard mainform/subform construction has the subform automatically
finding the related records when the mainform's record changes.

Check
the

properties of your subform control to see if you have the "parent" and
"child" fields set properly.

NOTE: this approach presumes you have well-normalized table structure.
"How" depends on "what" ... i.e., what data structure you have. You haven't
described your data/table structure...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi all
I have this code which will find the record on the main form.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

If IsLoaded("Invoices Register") Then
If Forms![INVOICEs REGISTER]![Invoice
details1].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "CpvId"
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![CpvId]
End If
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub




what if also i want it to find the record on the subform.
like this
me.cpvsubform.setfocus
DoCmd.FindRecord Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

I want the code after it finds the cpvid , to go to the record in the
subform which is the same in Forms![INVOICEs REGISTER]![Invoice
details1].Form![invoiceid]

i have tried to add this to the code but it didnt work ,if i keep

only
one

of them , code will work ( if i want to find only record of

subform ,
or

only
record of main form)

thanks

 

[mhmaid]

ok let me describe it like this

say I have received a batch of cash payment vouchers for a total amount of
10000 usd

cpv no.1: is for $ 1000 , this amount is a settlement of 2 invoices
inv no.747 is treat of patient jone for $ 700 , but it
belongs two visits for him,one visit is in 2007 , the other visit is in 2008
, like this
out of the $ 700 , $ 350 belongs to the first visit, the
balance to visit 2
for each of these two visits, the $ 350 is cost of two things
1.hospital charges for the period frm 1 to 30 oct 2007 for $ 200
2.Doctor fees for the date 15 oct 2007 for $ 150


so it is like this
1.one cpv contains many invoices
2.one invoice may pertains to more than one visit( each visit a separate
record in the table patients)
3.each amount that belongs to a visit may be for more than one expense
category

so , after i receive a batch i will enter batch data as follows
1.enter batch data in the table invoice register
* batch number
* batch date
* country
* etc.

these info is for the entire batch , then i will start entering the details
for each cpv
2.cpv 1 ( this is to be entered in the table cashPaymentVouchers
Cpvid which is auto number
Batchid which is same as the number in the main table " invoice register"
cpv number
cpv date
cpv amount

now , i want to enter the invoice details. its not practical to enter one
cpv number many times , that why i will enter invoice details in a sub table
"Invoices"

3.Entering invoice details in the table "invoices"

invoiceid which is auto no.
cpvid which is same as the related record in the cpv table
or let me make it looks like what i actully have

assume that i am entering batchid no 77

and also assume that i will enter cpv and invoice details for the patient
jone macdonald , who was sent two time for treatment to usa i.e.we have
already added two records ( two cases) to the table "Patients" which includs
his travel dates

table 1 ( cashPaymentVouchers)
cpvid 99
batchid 77 ( same as the related record in tbl invoice register)
cpv no.1
dated 01/12/08
cpv amount $ 1000
currency usd
exchange rate .3775
part2 ( invoices) this i enter thru a subform like this:
===================================================
cpvid InvoiceId Invoice Inv date Inv issuer
Invoice amount
99 889 A9458 01/01/07 hospital name here
500
99 74 879 15/02/07 Dr xxxx
500



now ,for each invoice there are other details, for this i have another form
and its subform , based on the other two tables

assuming that invoice no A9458 belongs to 2 visits for the patient , first
i will debit the visit of 01/01/07 with the amount that pertains to it , then
the other one

table 3 ( expense reports)
1.
expensereportid 8394
Invoiceid 889
Caseid 7340 jone macdonald visit of 01/01/07 ( debits account with only part
that belongs to same visit.
ExpenseReportAmount $ 300

now the table no 4 ( expense details) thru a subform:

ExpDetailId Exprptid ExpCat Expdescription dtfrom dateto
expItemAmount
say 738 8394 Treatm. Hospital chg 01/01/07 31/01/07
200
say 738 8394 treatm fees dr Ali 15/01/07 15/01/07
100


the next record in the tbl expense reports would be for the other visit ,
the part that belongs to the other visit of the same patient.

thanks

I'm still having trouble with the concept you describe as "expenses in four
tables". Can you explain this a bit more?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thanks for response

main table is patients ( it holds all the visits for the patients , may
hold more than one visit for a patient , let us call the visit , a case

Caseid
Patientnameid , this is numeric field , with lookup names from tblpatientnames
hospitalid
countryid
traveldate
returndate
diagnosisCategoryid, e.g.cancer,cardiac etc.
Diagnosisdetailsid .e.g.breast cancer,prostate cancer etc.
TreatingDoctor
CaseType,e.g.sent abroad,reimbursed,referred to a local hospital,etc.

and many other fields , these are the main

then

expense are separated over four tables

1.CashPaymentVouchrs ( cpv)

Cpvid
batchid from table Invoice register
cpvnumber
cpvdate
cpvAmount
Currency
Roe
notes

2.Invoices

Invoiceid
CpvId = from table cashpaymentvouchers
invoicenumber
invoicedate
invoiceIssuer
PatientnameId
invoiceamount

3.Expense reports
expensereportid
Invoiceid,from table invoices
Caseid , from table Patients
ExpRptAmount

4.Expense details
expensedetailid
Expensereportid
ExpenseCategoryid
expenseDescriptionid
expensedatefrom
expensedateto
expenseitemamount
accountingdate

if you see, i have a develped copy of the sample "expense reports" database,
i have renamed most of the fields, and I have added two more table for the
expenses to match with our requirments

when i start using access , with only the last two tables as the sample, I
faced alot of problems, one invoice may be for more than one case.so , i used
to enter the same invoice number two times , with part of invoice amount ,
that is why I added the table invoices.the same thing applies for the table
cpv, as one cpv may hold invoices for many patients.so without have the new
two tables, i used to enter the cpv details directly in the table expense
reports , and because at that stage i have also to enter the case id , so I
have to enter once cpv many times , but each time with part of the amount
that belongs to a case.

the relationship betw the table patients , and expense table is Caseid ,
which is in table 3, expense reports.


I have one more table ,Invoice register , which holds details of all
incoming invoice batchs

batchid
batchnumber
batchdate
countryid
batchamount
currency
dateBatchReleaseforpayment
etc.

when i receive invoices ,first i will enter batch detail in the invoice
register , then enter full details in the 4 expense tables as above.of course
when we send a patient , i will add a new case to the table patients.

i have many other tables like hospital, countries , Doctors etc. to save
space i use number fields in my tables like hospitalid, and lookup the list
from the hospitals ,same to countries etc.

I may have misunderstood your description...

It sounds like you have tables with the same (identical) structure, but used
to hold different "types" of (?)invoicing.

If this is your situation, your database is a spreadsheet! In a relational
database like Access, you'd use a single table with the fields, then add one
more to indicate "type".

I'm not confident yet that I understand your table structure, and in Access,
it all starts with the data. "How" depends on "what".

Could you post back a simple table structure description? For example, a
very rudimentary student enrollment data structure might look like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thank your for reply

my db is just a developed copy of the sample db "expense reports" which
comes with the windows office .the table "employees " has been renamed to
"Patients" and added /deleted many other fiels to match our needs.


first of all , instead of the two tables called "expense reports
","expense
details"
I have four tables ,which are these two plus another two tables
,"cashPaymentvouchers",Invoice


each table has its primary key
1.CashPaymentVouchers---CpvId
2.Invoices---InvoiceId
3.Expense Reports---ExpenseReportId
4.Expense Details---ExpensedetailId


the reason for adding the other two table is the following

my job is receiving , checking and accounting Invoices that belongs to
Patients treatment,Patients tickets ,etc.

our system is to add a record in the main table "Patients" for each type
we
send the patient abroad for treatment.the primary key for this table is
Caseid

we are sending patients to more than 20 countries , and in some of these
countries we receive the invoice directly , others we receive them thru
our
embassies in those countries

in the cases of invoices received thru our embassies , the embassy we pay
the bill ,we attach bill, draft copy or chq copy to a cash payment voucher
(CPV)
they will send the bill fortnightly , along with a covering letter , lets
call it a batch , so one batch will contain many cpv's , one cpv may
contain
many invoices , one invoice may belongs to more than one case



in order to enter each cpv one time only , and to enter each invoice one
time only , I have added these two table "cashpaymentvouchers","invoices"


apart from this , I have a fifth table which is called "Invoice Register"
this table is used to enter the data for each batch like batch number ,
country, batch amount , currency ,date received , date sent for payment.

when we arrange to send patient abroad , at this point I will add a record
to the table "Patients" which off course will include Travel date ,
country
of treatment , Hospital etc.

some of the expenses like advance allowances given to patient ,will be
accounted before the travel of the patient, these will not be entered in
the
Invoice Register, as there are no invoice here , but there are cpv , so ,
I
will first till you what i do in this case , then I will till you how I
enter
the Invoice details

we have arranged to sent a patient abroad
1.I will open the form"Expense reports by patient" sourceatients
table,this form got a subform "Patients subform", which will show the list
of
expenses for the patient.(the source of this subform is a union query for
the
four tables:cpv,invoice,Expense report,Expense details)

here I have a button when clicked will open another form
"Cashpaymentvouchers", with its subform"cpv subform" .source for the first
is
table cpv , souce of the 2nd is table "invoices.

my question was about this stage here , the form "CashPaymentVouchers"
source is this :SELECT DISTINCTROW [CashPaymentVouchers].* FROM
CashPaymentVouchers WHERE (([CashPaymentVouchers].Batchid=forms![Invoices
Register]!Batchid)) order by cpvid;

the code which I gave is in the on activate event of this form,when i open
,
i want it to go to the same cpv which was selected thru the
subform"patients
subform", and ALSO, to go to the same InvoiceId that was selected there,
off
course it cannot go directly to the invoiceid , first we have to select
the
cpv that this invoiceid is in.

so, now when i open the cashpaymentvouchers form , it will take me to

the

 

[Jeff Boyce]

I may be misinterpreting, but it sounds like you are entering data twice.
Is this related to "double injury bookkeeping"? If so, I have little
experience with working two sets of ledgers, so you might want to re-post to
get more eyes on your issue.

Also, when you name a table [Expense Report] the implication is you are
adding data to that table to create a report. This isn't necessary nor a
good idea in Access.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

ok let me describe it like this

say I have received a batch of cash payment vouchers for a total amount of
10000 usd

cpv no.1: is for $ 1000 , this amount is a settlement of 2 invoices
inv no.747 is treat of patient jone for $ 700 , but it
belongs two visits for him,one visit is in 2007 , the other visit is in 2008
, like this
out of the $ 700 , $ 350 belongs to the first visit, the
balance to visit 2
for each of these two visits, the $ 350 is cost of two things
1.hospital charges for the period frm 1 to 30 oct 2007 for $ 200
2.Doctor fees for the date 15 oct 2007 for $ 150


so it is like this
1.one cpv contains many invoices
2.one invoice may pertains to more than one visit( each visit a separate
record in the table patients)
3.each amount that belongs to a visit may be for more than one expense
category

so , after i receive a batch i will enter batch data as follows
1.enter batch data in the table invoice register
* batch number
* batch date
* country
* etc.

these info is for the entire batch , then i will start entering the details
for each cpv
2.cpv 1 ( this is to be entered in the table cashPaymentVouchers
Cpvid which is auto number
Batchid which is same as the number in the main table " invoice register"
cpv number
cpv date
cpv amount

now , i want to enter the invoice details. its not practical to enter one
cpv number many times , that why i will enter invoice details in a sub table
"Invoices"

3.Entering invoice details in the table "invoices"

invoiceid which is auto no.
cpvid which is same as the related record in the cpv table
or let me make it looks like what i actully have

assume that i am entering batchid no 77

and also assume that i will enter cpv and invoice details for the patient
jone macdonald , who was sent two time for treatment to usa i.e.we have
already added two records ( two cases) to the table "Patients" which includs
his travel dates

table 1 ( cashPaymentVouchers)
cpvid 99
batchid 77 ( same as the related record in tbl invoice register)
cpv no.1
dated 01/12/08
cpv amount $ 1000
currency usd
exchange rate .3775
part2 ( invoices) this i enter thru a subform like this:
===================================================
cpvid InvoiceId Invoice Inv date Inv issuer
Invoice amount
99 889 A9458 01/01/07 hospital name here
500
99 74 879 15/02/07 Dr xxxx
500



now ,for each invoice there are other details, for this i have another form
and its subform , based on the other two tables

assuming that invoice no A9458 belongs to 2 visits for the patient , first
i will debit the visit of 01/01/07 with the amount that pertains to it , then
the other one

table 3 ( expense reports)
1.
expensereportid 8394
Invoiceid 889
Caseid 7340 jone macdonald visit of 01/01/07 ( debits account with only part
that belongs to same visit.
ExpenseReportAmount $ 300

now the table no 4 ( expense details) thru a subform:

ExpDetailId Exprptid ExpCat Expdescription dtfrom dateto
expItemAmount
say 738 8394 Treatm. Hospital chg 01/01/07 31/01/07
200
say 738 8394 treatm fees dr Ali 15/01/07 15/01/07
100


the next record in the tbl expense reports would be for the other visit ,
the part that belongs to the other visit of the same patient.

thanks

I'm still having trouble with the concept you describe as "expenses in four
tables". Can you explain this a bit more?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thanks for response

main table is patients ( it holds all the visits for the patients , may
hold more than one visit for a patient , let us call the visit , a case

Caseid
Patientnameid , this is numeric field , with lookup names from tblpatientnames
hospitalid
countryid
traveldate
returndate
diagnosisCategoryid, e.g.cancer,cardiac etc.
Diagnosisdetailsid .e.g.breast cancer,prostate cancer etc.
TreatingDoctor
CaseType,e.g.sent abroad,reimbursed,referred to a local hospital,etc.

and many other fields , these are the main

then

expense are separated over four tables

1.CashPaymentVouchrs ( cpv)

Cpvid
batchid from table Invoice register
cpvnumber
cpvdate
cpvAmount
Currency
Roe
notes

2.Invoices

Invoiceid
CpvId = from table cashpaymentvouchers
invoicenumber
invoicedate
invoiceIssuer
PatientnameId
invoiceamount

3.Expense reports
expensereportid
Invoiceid,from table invoices
Caseid , from table Patients
ExpRptAmount

4.Expense details
expensedetailid
Expensereportid
ExpenseCategoryid
expenseDescriptionid
expensedatefrom
expensedateto
expenseitemamount
accountingdate

if you see, i have a develped copy of the sample "expense reports" database,
i have renamed most of the fields, and I have added two more table for the
expenses to match with our requirments

when i start using access , with only the last two tables as the sample, I
faced alot of problems, one invoice may be for more than one case.so ,

i
used

to enter the same invoice number two times , with part of invoice amount ,
that is why I added the table invoices.the same thing applies for the table
cpv, as one cpv may hold invoices for many patients.so without have

the
new

two tables, i used to enter the cpv details directly in the table expense
reports , and because at that stage i have also to enter the case id ,

so
I

have to enter once cpv many times , but each time with part of the amount
that belongs to a case.

the relationship betw the table patients , and expense table is Caseid ,
which is in table 3, expense reports.


I have one more table ,Invoice register , which holds details of all
incoming invoice batchs

batchid
batchnumber
batchdate
countryid
batchamount
currency
dateBatchReleaseforpayment
etc.

when i receive invoices ,first i will enter batch detail in the invoice
register , then enter full details in the 4 expense tables as above.of course
when we send a patient , i will add a new case to the table patients.

i have many other tables like hospital, countries , Doctors etc. to save
space i use number fields in my tables like hospitalid, and lookup the list
from the hospitals ,same to countries etc.


:

I may have misunderstood your description...

It sounds like you have tables with the same (identical) structure,

but
used

to hold different "types" of (?)invoicing.

If this is your situation, your database is a spreadsheet! In a relational
database like Access, you'd use a single table with the fields, then

add
one

more to indicate "type".

I'm not confident yet that I understand your table structure, and in Access,
it all starts with the data. "How" depends on "what".

Could you post back a simple table structure description? For

example,
a

very rudimentary student enrollment data structure might look like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

thank your for reply

my db is just a developed copy of the sample db "expense reports" which
comes with the windows office .the table "employees " has been

renamed
to

"Patients" and added /deleted many other fiels to match our needs.


first of all , instead of the two tables called "expense reports
","expense
details"
I have four tables ,which are these two plus another two tables
,"cashPaymentvouchers",Invoice


each table has its primary key
1.CashPaymentVouchers---CpvId
2.Invoices---InvoiceId
3.Expense Reports---ExpenseReportId
4.Expense Details---ExpensedetailId


the reason for adding the other two table is the following

my job is receiving , checking and accounting Invoices that belongs to
Patients treatment,Patients tickets ,etc.

our system is to add a record in the main table "Patients" for

each
type

we
send the patient abroad for treatment.the primary key for this

table
is

Caseid

we are sending patients to more than 20 countries , and in some of these
countries we receive the invoice directly , others we receive them thru
our
embassies in those countries

in the cases of invoices received thru our embassies , the embassy

we
pay

the bill ,we attach bill, draft copy or chq copy to a cash payment voucher
(CPV)
they will send the bill fortnightly , along with a covering letter

,
lets

call it a batch , so one batch will contain many cpv's , one cpv may
contain
many invoices , one invoice may belongs to more than one case



in order to enter each cpv one time only , and to enter each

invoice
one

time only , I have added these two table "cashpaymentvouchers","invoices"


apart from this , I have a fifth table which is called "Invoice Register"
this table is used to enter the data for each batch like batch

number
,

country, batch amount , currency ,date received , date sent for payment.

when we arrange to send patient abroad , at this point I will add

a
record

to the table "Patients" which off course will include Travel date ,
country
of treatment , Hospital etc.

some of the expenses like advance allowances given to patient ,will be
accounted before the travel of the patient, these will not be

entered
in

the
Invoice Register, as there are no invoice here , but there are cpv

,
so ,

I
will first till you what i do in this case , then I will till you

how
I

enter
the Invoice details

we have arranged to sent a patient abroad
1.I will open the form"Expense reports by patient" sourceatients
table,this form got a subform "Patients subform", which will show

the
list

of
expenses for the patient.(the source of this subform is a union

query
for

the
four tables:cpv,invoice,Expense report,Expense details)

here I have a button when clicked will open another form
"Cashpaymentvouchers", with its subform"cpv subform" .source for

the
first

is
table cpv , souce of the 2nd is table "invoices.

my question was about this stage here , the form "CashPaymentVouchers"
source is this :SELECT DISTINCTROW [CashPaymentVouchers].* FROM
CashPaymentVouchers WHERE (([CashPaymentVouchers].Batchid=forms![Invoices
Register]!Batchid)) order by cpvid;

the code which I gave is in the on activate event of this

form,when i
open

,
i want it to go to the same cpv which was selected thru the
subform"patients
subform", and ALSO, to go to the same InvoiceId that was selected there,
off
course it cannot go directly to the invoiceid , first we have to select
the
cpv that this invoiceid is in.

so, now when i open the cashpaymentvouchers form , it will take me

to
the