At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.
at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..
they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..
what i want to do is allow the user to enter the customer details and the booking details in one form..
i assume i would have to create a third form based on a query..
if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?
1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..
2. this id will then need to be auto placed into the foreign key customer id field in the booking table.
I've sent a post about this before and got some of the answer from 'Dennisk'- thanks.
I want to replace composite keys with a primary. I've just made an autonumber field on one table for my new primary key and already populated it. On the foreign table ive made a new field with 'number' datatype; this field is empty at the moment.
I'm not sure how to update the foriegn tables new field with the primary tables data. I've saw it before and remember it being easy, just can't remember how to do it.
I obviously want the numbers in the primary key field to relate to the correct records in the foriegn table.
Anyone know what the query is or if there's another way?
I have a visual basic function, placed in a module. I need to call it from a form field, and pass a value to it and the function has to return a value.
I tried it but it doesn't work the value I get on the field is " #Name? "
i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:
tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.
My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:
tblTasks
TaskID - PK Task Name Team ID - FK
tblTeam TeamID - PK Team Name
there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.
I have a form where all records are listed on one page with a vertical scrollbar (all simple textboxes):
---------------------------------------------- PrimaryKey Name ForeignKey ---------------------------------------------- 1 Pam 20 2 John 20 3 Kim 20 4 Larry 20
... underneath these on the form is a blank record for NEW additions, which obviously has nothing in any of its textboxes, waiting for user to input some data, as normal.
The Foreign Key, due to the nature of my DB, will always be the SAME value for each record in the DB (don't ask, it's part of an export/import-to-main-db app). So I know that the NEW record, if created, will have the same value as the rest of the records.
How do I get it so that when a new record is created, the Foriegn Key textbox is automatically filled in with the value from the row above? (Due to referential integ reasons the Foreign Key field MUST be filled in accuratly or else an error ocurrs.)
Looking at the various events, I realise that it is the BeforeInsert event that will sort this out, but I'm at a loss regarding how to get the NEW record's Foreign Key textbox to match the Foreign Key value above it.
I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.
Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.
(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).
Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.
I have written a basic query that looks at the date field, department field and cost the cost field on a table. What I would like is a form that you can chose a date (19/09/2005) and a it looks at the table and calculates the total sum of money for each department for this date. Ie.
Hello all This is a pretty basic query I guess but I am having trouble solving this.. Can anyone help plsssssssss. I have two tables Table1 Code Serial_No Amount1 Amount2 1 ABC 100 200 1 ABC 720 220 4 ABC 150 112 9 PQR 11 25 9 ZZZ 122 44 9 ZZZ 1250 42
Table2 Code Serial_No Amount1 Amount2 1 ABC 24 20 1 PPP 72 22 4 ABC 15 12 9 PQR 11 54 9 ZZZ 22 44 9 ZZZ 150 99 and total of the amount as 844 and 440
I have write a query to select only the Code common to both the tables and add total of amounts for these codes. For eg. I want rows Outpput 1 ABC 100 200 1 ABC 720 220
I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?
Background:I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.
I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
Hello, i have a small database thats purpose is to show when a book has been returned to me. I am recording the date that books are loaned out. Books are loaned for a 2 week period. I dont know if it would be easier to just manually put in the date that books should be returned (so that means just manually calculate what the date will be 2 weeks later). The reason for this is because i want a query that shows when the date has passed and how many days overdue the book is. Would this be difficult to achieve? What i was thinking is a query that calculates when the 2 week period is up and adds the unique number for book in a report.
I am trying to incorporate a field in my customer order form for "Invoice Number". Not every customer order form will have an invoice number.
The Invoice Number field will default as null, or blank. I want to make it so the Invoice Number gets automatically populated ONLY IF another field on the same form is populated with "X".
Also, I would like the Invoice Number to equal the Previous Record that has an invoice number + 1. For example, if the previous 5 client records did not have an invoice number (blank because the client's form did not have a certain field populated with "X"), but the previous 6th record had an invoice number of 1001, then the current invoice number would be 1002.
So far, I just have the Form for the underlying table created. I don't have the invoice field in the table created yet. Not too sure where to start.
This is probably really basic stuff to most of you, but here goes.
(In the past, I have always had tables with zillions of fields, rather than having linked tables, so I have never had to mess with this stuff before...)
I have a db with 2 tables. A Main table with, say, contact names and addresses and a Lesser table with specialised info that I only want for some of those contacts.
The Primary Key in the Main table is fldContNum (autonumber), the primary key in the Lesser tables is fldNum. The two tables are linked 0ne-to-one on those fields.
There is not a record in the Lesser table for every record in the Main table.
I have a form showing records from the main table and a sub-form showing records from the Lesser. They are linked Child fld Num / Master fldContNum.
At the moment, I can go to a Contact from the main table and I can enter info for him in the sub-form, thus creating a record in the lesser table. That works ok.
However, if I move the main form to another contact and then go back, that info that I just entered does not show in the subform. In other words the subform is in "add new record" mode, whilst I want it to go automatically to the existing "Lesser" record with that number.
I can make it do this with VBA and searching recordsets and suchlike easily enough, but I feel that there must be a much more basic way of linking the two form and tables.
Also I want to make sure that if there is already a "Lesser" record with that number, I want to make it so that the sub form cannot even try to make a new record (because then I obviously get primary key violations).
I hope that I have made myself relatively clear here...
Greetings, I'm a bit new to the world of MS Access and have encountered a problem when making a search form for my database. The query is using a Like Condition with the '*' Wildcard for a single field like so:
Like "*" & [Forms]![frmPickUpReport]![txtAgentName].[Text] & "*"
While the query on its own runs correctly from the viewer of course prompting me to enter the data manually the actual data from the form is being ignored. Instead of taking the text value within my form's text box, when I press the report creation button the same data box is prompted to me asking for me to enter the data manually.
anybody has a solution?
I'm using MS Access 2000 and cannot use any other application such as visual studion
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
I have two tables; "Section_Failure_Mode" and "Risk_Assessment". They are linked by a common field called section_failure_mode_id. I would like to write a query where you can delete every record in "Risk_Assessment" where the checkbox (called Selected) is not selected (i.e. equal to 0) in the table "Section_Failure_Mode". However, I do not want any records to be deleted from "Section_Failure_Mode", only from "Risk_Assessment".
I have put a link to the screen cap of the query I have tried below. Is this the correct way to do it?
I have an access Query(named newSerial) : SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase ORDER BY (Productbase.Serialnumber+1) DESC;
I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form? It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067. I tried: outbox=Expr1 ' gives null 'or outbox=[newserial].[Expr1] ' which gives "Access cant find the field "|" refered to you in your expression"
What should I write? Whats the correct expression?