If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
General Table/Input Data Question
have a bit of a problem for a small little project i wish to finish and i am
stuck on a minor detail. Basically this little project has to keep track of receipts Company Name Vat Number Date of Receipt Amount of Transaction the easy way of doing it of course is just straight input it into the table (either directly or via the use of a form to mask the entry into the table in a pretty way) I have gone the form route and well i have discovered that i have several receipts from the same company which means i have to enter the company name and Vat number over and over again. which is time consuming as you can imagine i could create a companies table and have the form pull the info from this table. BUT if there is a new company i have to go to the company table input it there and then go to the form to fill out the rest is there a way (im sure there is.. i just cant get my head around it right now) to have the form pull up the company and vat data from a table, if that particular data does not exist allow you to input it on the spot and then, let you fill in the rest date and amount of transaction ? thanks in advance.. Sid |
#2
|
|||
|
|||
General Table/Input Data Question
Sid
Check Access HELP NotInList and LimitToList. HELP has syntax and examples you can adapt. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sid" wrote in message ... have a bit of a problem for a small little project i wish to finish and i am stuck on a minor detail. Basically this little project has to keep track of receipts Company Name Vat Number Date of Receipt Amount of Transaction the easy way of doing it of course is just straight input it into the table (either directly or via the use of a form to mask the entry into the table in a pretty way) I have gone the form route and well i have discovered that i have several receipts from the same company which means i have to enter the company name and Vat number over and over again. which is time consuming as you can imagine i could create a companies table and have the form pull the info from this table. BUT if there is a new company i have to go to the company table input it there and then go to the form to fill out the rest is there a way (im sure there is.. i just cant get my head around it right now) to have the form pull up the company and vat data from a table, if that particular data does not exist allow you to input it on the spot and then, let you fill in the rest date and amount of transaction ? thanks in advance.. Sid |
#3
|
|||
|
|||
General Table/Input Data Question
Sid:
You really do need a Companies table to avoid redundancy in the receipts table. A table which stores data redundantly is not correctly normalized and at risk of inconsistent data. The Companies table would be like this: Companies ….CompanyID (autonumber primary key) ….CompanyName ….VATNumber ….more columns for address etc In the Receipts table just include a CompanyID column as a foreign key of long integer number data type (not an autonumber this time), removing the redundant CompanyName and VATNumber columns, and create a relationship from Companies to Receipts, enforcing referential integrity. In the Receipts form use a combo box as the control for the CompanyID field and set up the combo box as follows: Name: cboCompany ControlSource: CmpanyID RowSource: SELECT [CompanyID], [VATNumber], [CompanyName] FROM [Companies] ORDER BY [CompanyName]; BoundColumn: 1 ColumnCount: 3 ColumnWidths: 0cm;0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert them. The important thing is that the first two dimensions are zero to hide the first two columns and that the third is at least as wide as the combo box. Add an unbound text box to the form, with a ControlSource property of: =[cboCompany].Column(1) This will show the VAT number for the selected company. The Column property is zero-based, so Column(1) is the second column, VATNumber. You can add a new company directly via the combo box by typing in the new company name. To facilitate this you include the following code in the combo box's NotInList event procedu Dim ctrl As Control Dim strMessage As String Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then DoCmd.OpenForm "frmCompanies", _ DataMode:=acFormAdd, _ WindowMode:=acDialog, _ OpenArgs:=NewData ' ensure frmCompanies closed DoCmd.Close acForm, "frmCompanies" ' ensure company has been added If Not IsNull(DLookup("[CompanyID]", "[Companies]", _ "[CompanyName] = """ & NewData & """")) Then Response = acDataErrAdded Else strMessage = NewData & " was not added to Companies table." MsgBox strMessage, vbInformation, "Warning" Response = acDataErrContinue ctrl.Undo End If Else Response = acDataErrContinue ctrl.Undo End If The above code opens a form frmCompanies, which you'll need to create, bound to the Companies table, and passes the new company name to it. In the form you can then enter the VAT number, address details etc for the new company, and when you close the form you'll be returned to the receipts form with the new company added to the combo box's list. In the Open event procedure of frmCompanies put the following code: If Not IsNull(Me.OpenArgs) Then Me.[CompanyNamee].DefaultValue = """" & Me.OpenArgs & """" End If This sets the default value of the CompanyName control to the value passed to the form as its OpenArgs property. Ken Sheridan Stafford, England Sid wrote: have a bit of a problem for a small little project i wish to finish and i am stuck on a minor detail. Basically this little project has to keep track of receipts Company Name Vat Number Date of Receipt Amount of Transaction the easy way of doing it of course is just straight input it into the table (either directly or via the use of a form to mask the entry into the table in a pretty way) I have gone the form route and well i have discovered that i have several receipts from the same company which means i have to enter the company name and Vat number over and over again. which is time consuming as you can imagine i could create a companies table and have the form pull the info from this table. BUT if there is a new company i have to go to the company table input it there and then go to the form to fill out the rest is there a way (im sure there is.. i just cant get my head around it right now) to have the form pull up the company and vat data from a table, if that particular data does not exist allow you to input it on the spot and then, let you fill in the rest date and amount of transaction ? thanks in advance.. Sid -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
General Table/Input Data Question
"Sid" wrote in message ... have a bit of a problem for a small little project i wish to finish and i am stuck on a minor detail. Basically this little project has to keep track of receipts Company Name Vat Number Date of Receipt Amount of Transaction the easy way of doing it of course is just straight input it into the table (either directly or via the use of a form to mask the entry into the table in a pretty way) I have gone the form route and well i have discovered that i have several receipts from the same company which means i have to enter the company name and Vat number over and over again. which is time consuming as you can imagine i could create a companies table and have the form pull the info from this table. BUT if there is a new company i have to go to the company table input it there and then go to the form to fill out the rest is there a way (im sure there is.. i just cant get my head around it right now) to have the form pull up the company and vat data from a table, if that particular data does not exist allow you to input it on the spot and then, let you fill in the rest date and amount of transaction ? thanks in advance.. Sid |
#5
|
|||
|
|||
General Table/Input Data Question
|
Thread Tools | |
Display Modes | |
|
|