dazz2302
07-21-2010, 12:51 AM
Hi folks
I am teaching myself to use Access and riding a steep learning curve however I need help. I am working in version 2010.
I need to build a dbase for work. I have created two tables named INVOICEST and CONTRACTST and created two forms one called INVOICESF and the other CONTRACTSF.
The CONTRACTST table records contract details and each contract is given a unique CRC reference number to identify the contract. In the CONTRACTST table two field names in particular are named CRCREFERENCE and CONTRACTAMT.
The INVOICEST table logs invoice details to be paid; note not all the invoices relate to contracts. Two fields in the INVOICEST table are also named CRCREFERENCE and CONTRACTAMT.
The idea is that when an Invoice is recorded into the INVOICESF form and it relates to a contract, a CRC reference number is typed into the CRCREFERENCE field and as a result, the total amount of the contract will automatically show up in to CONTRACTAMT field in other words If the CRC reference number on the INVOICESF form matches the CRC reference number in the CONTRACTST table, it returns the contract amount from the CONTRACTST table to the CONTRACTAMT field in the INVOICESF form.
I have linked the two tables togther by the CRCREFERENCE field. In the CONTRACTST table it is a manditory field which is unique so I have this as a primary key linked to the INVOICEST table where the field can be left blank if an invoice is not related to a contract.
The CRCREFERENCE field is a text field and the CONTRACTAMT field is a currency field.
How do you return a field value based on the criteria when two fields one in a form and the other in a table match? What function is best to use as a control source to perform this, IIF or DLOOKUP?
I hope I haven't confused you and have supplied enough information. I have tried but nothing seems to work for me.
Any help would be graetly appricated
Thank you very much!
Kind regards
Dazz
I am teaching myself to use Access and riding a steep learning curve however I need help. I am working in version 2010.
I need to build a dbase for work. I have created two tables named INVOICEST and CONTRACTST and created two forms one called INVOICESF and the other CONTRACTSF.
The CONTRACTST table records contract details and each contract is given a unique CRC reference number to identify the contract. In the CONTRACTST table two field names in particular are named CRCREFERENCE and CONTRACTAMT.
The INVOICEST table logs invoice details to be paid; note not all the invoices relate to contracts. Two fields in the INVOICEST table are also named CRCREFERENCE and CONTRACTAMT.
The idea is that when an Invoice is recorded into the INVOICESF form and it relates to a contract, a CRC reference number is typed into the CRCREFERENCE field and as a result, the total amount of the contract will automatically show up in to CONTRACTAMT field in other words If the CRC reference number on the INVOICESF form matches the CRC reference number in the CONTRACTST table, it returns the contract amount from the CONTRACTST table to the CONTRACTAMT field in the INVOICESF form.
I have linked the two tables togther by the CRCREFERENCE field. In the CONTRACTST table it is a manditory field which is unique so I have this as a primary key linked to the INVOICEST table where the field can be left blank if an invoice is not related to a contract.
The CRCREFERENCE field is a text field and the CONTRACTAMT field is a currency field.
How do you return a field value based on the criteria when two fields one in a form and the other in a table match? What function is best to use as a control source to perform this, IIF or DLOOKUP?
I hope I haven't confused you and have supplied enough information. I have tried but nothing seems to work for me.
Any help would be graetly appricated
Thank you very much!
Kind regards
Dazz