I am building a contacts database - brief structure as below:
tblOrganisation - details pertaining to the company
tblContact - Primary key is ContactID (autonumber) and details about the contact
tblContactDetails - Primary Key is DetailsID (Autonumber), Foreign Key of ContactID and fields to store various sets of details.
What I want to do is each time a contact is added I want to create an entry in tblContactDetails with their ContactID as the foreign key. I have created a form based on tblOrganisation, with a subform in Datasheet view showing the contact info (Name etc) which gives the + symbol to access the contact details. Trouble is if there is not an already entry in tblContactDetails with the same ContactID then there is no way I can add details.
The situation is that we have a split DB on a LAN using Windows Server. The BE is on the server and the FE are distributed to each user PC on the LAN. (For historical reasons there are differing versions of Access at each user which was the primary motivation to split the DB)
We also have a copy of the DB on the company website which is to be updated via FTP on a regular basis. This is used for queries only by visitors. We now want our clients to be able to update their information on line in tandem with our local users.
The question is - what is the best method to keep these DBs synchronised?
My thought is to use replication with the LAN BE as the master DB and the website having a replicated version. Then we can periodically download the website version and synchronise the two - presumably we would have to disable the updating function on the website while we do this and then reload the new replicated version up to the website.
A problem I see already is that the website DB is not split where the local version is - is it possible to rejoin a split database? Synchronise it, upload a a replica and then split the BE and FE again?
Thought I had sorted this, but now experiencing another problem. I am creating invoices from my db, invoices may have more than one line. So I have set up a form with client name, invoice number, invoice date on the main part of the form, then the invoice lines as a sub form. The two parts are not synchronising. I want the combo box to find the client for me. I suspect I have something that is really quite simple but fundamentally wrong with this combo box. And I may well have made a mess of it because I have fiddled with it so much. I am attaching the db so someone can take a look. My invoice form is frmInvoicehdr. Thanks in anticipation.
As a field engineer often I am away from base, currently I am using excel to record hours serial numbers and stock codes used on relevant jobs. This I understand is a flat table nuff said.
Can I create a form in access using the unique a job identifier (taken from the main database)that can be carried remotley from the main server (laptop) as a template which is then populated at the workplace and uploaded to the main server database when myself or my colleagues are in the head shed (main office)??
I am a moderatley new user to access so please be gentle. any and all help a big bonus..:o
I'm pretty hopless with VB but I am trying very hard (hours spent on this forum reading code I only slightly get the gist of) to implement a form that takes input from two synchronised unbound combo boxes and then displays records matching the input in a sub-form in datasheet mode. I'm failing at the first part just now: the combo boxes. No doubt I'll fail at the second part too but I'll deal with that when I get this fixed. :)
I was using the northwind DB to learn how to synchronise the combo boxes. I successfully did it by following the instructions and using this code from MS kb article 209595 in the After Update procedure:
Me.Products.RowSource = "SELECT ProductName FROM" & _ " Products WHERE CategoryID = " & Me.Categories & _ " ORDER BY ProductName" Me.Products = Me.Products.ItemData(0)
I then tried to implement this on my database by changing the relevant information to reflect my database info. as shown below, and it does not work. I switched Product references for my Unit references and Category references for my Item references but I don't think I got it right or over-simplified it due to the table structure.
Private Sub Item_AfterUpdate() Me.Units.RowSource = "SELECT Unit FROM" & _ " Units WHERE ItemID = " & Me.Items & _ " ORDER BY Unit" Me.Units = Me.Units.ItemData(0) End Sub
When I compile the code it stops at Me.Items with the error "Method or data member not found". So I assume that I have not referenced the right objects when I modified the code. I thought this was maybe due to the two tables I'm using being in a many-to-many relationship via a junction table, as opposed to the northwind tables which are directly linked by a one-to-many relationship. If this is indeed the case, can anyone help me put this right. I have attached my table structure / relationships in a .jpg to this post.
Any help from all you smart developer dudes would be muchly appreciated.
Basically in my order details table i have the following fields
Product Unit Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT Grasshopper Box1000 Adult Grasshopper Box1000 Subadult Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
i would like to sum all the column fields in a tables column where an id is the same as the id in that table. I know you can use the SUM function in the select statement of your query.
But how can i set this sum result into a variable, so i can use it for calculation further on in the program?
I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:
1) Location [currently in the field is "MAX"] 2) CalendarYear [currently in the field is "2014"] 3) NextWorkorderNumber [currently in the field is "1"]
I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:
a. I need the field to read as followed: Max-2014-00001 b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002]. c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.
I have two tables called Hardware and Desk. They both have two fields called ChangeID and Priority.
What I would like to do is set it so that if we change the Priority in a record existing/created in 'Hardware', the Priority of the related record in 'Desk', also changes.
I have a database with various tables, customers, jobs_table, black_book. I am wanting to have a field that looks up a latest date.
Both the [jobs_table] and [black_book] have a date field, there can be multiple entries per customer in both fields. What I want to do is have 2 fields in the [customer] table that looks up the customer name in [jobs_table] and [black_book] and brings back the latest date.
I have a database with few tables but I'm having a problem with using different fields from different tables. When I try to set a Validation Rule for Date field in Tbl1 to be Between Date2 And Date3 from Tbl2, I use the following expression: Between [Tbl2]![Date2] And [Tbl2]![Date3] I place this in the validation Rule field in the Table edit, but I always get error "no such field..." :mad: what's the matter?
Also when I want to set a default value for a field that's calculated from different fields from different tables, it always shows me an error about "automation object"
Hey, I have two tables, one with order information and another with client details. The primary key in each of them is a 'ClientID' field. As well as the two tables, I have forms letting you enter data into each table. On the booking form i want to be able to look up the Client ID based on a customers surname. Can I do this?
I am extreme noob at access. Ill just toss that out right now. I am reading as much as I can, but with the wealth of info out there, there is still a disconnect with my style of learning. I am trying to create a simple inventory database for my work. I have looked at the inventory templates and they overwhelm me. Way to complex at the moment for me trying to understand this. So i set out to learn and build and grow my database functionality as I go along.
With that, I have a question. I came across some reading material that suggested it is possible to have multiple fields as a primary key. Allowing you to have duplicates in each field, but no duplicates across the entire group of fields.They didnt explain it much, but I gathered it to mean as much with Field 1 and 2 as PK's
Code: Field 1 | Field 2 1 2 2 1 1 3 1 2 -----> This would be a duplicate of the first
Is this correct?I have not found out how to make multiple PK's though. How is this type of behavior achieved?
I have a field1 in table1 & field2 in table2. (field1&2 are numbers) After designing the tables I linked them with an ID (which is primary key in table1).
When I created a tabular form based on these two tables & tried to enter data, two problems.
1) Error come unable to update / enter & help says its locked while when I check the property of Locked, it is set as 'No' & Enabled 'Yes'. Why I can't input data.
2) The calculation field2/field1*1000 does not appear in a textbox (where it is defined).
Anyone can help me how to link fields in different tables. Im creating a databse that has an ID. and that ID will be same with the ID on the other table.
Table 1 ID = 123
Table 2 ID = link to (Table 1 ID) and when i input characters on the Table 1 ID field it will appear or inputed also at Table 2 ID field.
I have an Access 2000 database with several tables in it. Against each field in each table I would like to set every REQUIRED and INDEXED value to NO. Is there a way I can do this using VBA?
If it helps, one of the tables has 2 fields in it named TABLE NAME and FIELD NAME with every field against every table listed.
I am a firefighter who has been tasked with creating a database to track repairs and inventory on breathing apparatus(SCBA). There are several(5) related pieces of equipment that I think should go on the same table. Three of these will have nearly identical fields. The other two will have additional field specific to them. Should I create one table including all five pieces and just leave the extras fields blank or is there a better way. Thanks.
it's been a good few years since i have used Access and my memory seems to be failing me hope some one can help:
im trying to set a feild that Selects a value from a dropdown box with a set of values , whihc is defined by the results of a previous field selection.
i.e i select an employee from drop down list the next field is the National insurance number, which i want to auto populate based on the answer from the previous table.
also along the same route im trying to make a similar table that when you pick a particular brand of motor car it will populate the next drop down field/box based on the models avalible to that brand.
in both cases i have seperate tables, i presume that i need to make some quieries and some vba code to allow the correct selection but i really don't know where to start.
I am new to this forum and a beginner to intermediate with Access.
I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.
