find record in main form then find record in the subform


M

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
 
Ad

Advertisements

J

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/
 
M

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" source:patients
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



Jeff Boyce said:
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 said:
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
 
J

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/

mhmaid said:
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" source:patients
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



Jeff Boyce said:
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 said:
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
 
M

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.


Jeff Boyce said:
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/

mhmaid said:
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" source:patients
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



Jeff Boyce said:
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
 
J

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/

mhmaid said:
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.


Jeff Boyce said:
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/

mhmaid said:
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" source:patients
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
 
Ad

Advertisements

M

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






Jeff Boyce said:
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/

mhmaid said:
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.


Jeff Boyce said:
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" source:patients
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
 
Ad

Advertisements

J

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/

mhmaid said:
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






Jeff Boyce said:
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/

mhmaid said:
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" source:patients
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top