I have a problem concerning how to display records dependant or depending on the current record.
Basically I have a seperate area within the db that gives you the option to set which record(s) depends on or dependant to another record(s) as primary and secondary
When trying to display this info, it is fine for a direct primary - secondary within my listbox, but the complication occurs when the secondary record could also be a primary record of another record (if that makes sense).
for example. The data is as such,
PrimaryID SecondaryID
10 12
12 13
15 10
So from this data, my list box should show 1 record that this record depends on (15) , and in another list box 2 records than depend on this record (12,13)
I'm wondering if some kind of loop code would do the trick but not sure what angle to attack this at.
I would appreciate it if anyone knows a simple solution to this.
The Total Ongoing Calls The Ongoing Calls in Area 1 The Ongoing Calls in Area 2
Ideally the Total Ongoing Calls should equal the other two but this is never the case due to calls in wrongs areas etc. To display this I have the Total Ongoing Calls shown as an Area Chart on the Primary Axis with the other two figures shown as a Stacked Area Chart on the Secondary Axis.
This way if I manually fix the Primary and Secondary Axis Scale to the same then I can see at a glance the total in both areas and the discrepancy from the Total. Great.
The issue is that the Primary Axis will almost always be slightly higher than the Secondary Axis which means that whenever the Scale changes I would have to manually change the other Axis. Is there any way, programaticallly or otherwise, that I can link the two Axis together so that if one changes up or down the other changes with it.
Thanking everyone I am about to confuse in advance.
Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.
I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.
I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?
Hi I have a main table in my database. For each record in this table there may exist 0, 1 or many records linked to it in a secondary table. I have created a form for viewing the records in the main table, and now want to link to a second form that displays the records in the secondary table, if indeed any exist for the main record being displayed on form1. Specifically . . If I can detect whether secondary records exist for each record in the first, how can I pass a parameter (ie Table1id) to the secondary form so that we can view only the secondary records associated with the main record selected ? thanks ajm
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers. Now , I try to add the "price" field of each record of the secondary form and then show the result in a text box in the main form, so that I can have a total price amound for each producer.I've tried many things but nothing seems to work. Is this so difficult to achieve? Pls help. Thank you in advance!
I have a form that is bound to a table called "date". Date has to fields "date_id" which is a numeric value and "date_description" which is the name of the month. A secondary form is bound to a table called "Employees" which has fields regarding data about the emploees of a company (eg "surname", name" etc). I use these 2 formes together to select the month of year and show/edit the data of the employees. Everything work perfectly except one last thing. Is it possible that after I edit (or add) a record in the secondary form (table "employees") the secondaty form to be sorted by "surname"? How can I do such a thing? I would be grateful if you give me an example. Thank you in advance
I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.
Imagine you have a split database with two users making reports on their own front end. Lets say user 1 wants to share a report they built with user 2. is it possible to send this Object through vba so that user 2 would received a working editable copy that they can use on their front end without sharing the actual accdb front ends?
I thought about creating a Database that would just host the reports but again if I cannot send or access secondary database objects then this method would not work.
I am maintaining LESSON using a simple form. On creating each new LESSON record, I also wish to insert a row into PAYMENT, using values from the LESSON table - Payment_ID(autonumber), Lesson_ID = LESSON.Lesson_ID, Payment_Date = LESSON.Lesson_Date
What is the best way to do this? I have tried to add an Event Procedure to 'Before Insert' but I can't seem to get the syntax correct. Also, for this to work, do I need to paint all fields on LESSON form including (hidden) PK?
I have simplified the tables above but they are relevant to what I am trying to do. Any help would be much appreciated- am new to Access...rather frustrating.
Many thanks, Simon.
PS I have searched through existing messages but can't see one that answers this, I apologise if this has been answered before- just point me to the orig post.
I built an Append Query to take records of 'Leavers' from my Primary Table and add them to a Secondary Table named 'Leavers. This worked perfectly, but on reflection I determined that I needed to append a further column 'Notes' which exists in the Main Table but not in the Secondary Table.I amended the SQL statement as follows, but the query now fails stating that it doesn't recognize the field 'Notes'.
Does this mean that one would need to recreate a new Secondary Table to incorporate the additional field? I have attempted to edit the secondary table by merely adding the 'Notes' field but that doesn't seem to be possible.
I have a form where I am entering orders. For this, there is only 1 item and 1 customer per order. The Order_Table is the 'general' of the form. However, I want a Customer_name dropdown on the form, and return data to 'display' on the form (but capturing and ONLY storing the Customer_no), once the user makes a selection.
Additionally, in the same vein, I want to have an Item_name list, where the user selects one from the listing, and display (again, no edit) the name. Again, only want to store the Customer & Item numbers on the Order table.Here's the 'general' table layout (I've taken out what doesn't matter):
What I'd like to do now is add a second function that will make all the controls on the form go to null after the record is saved. so the user can start from scratch and add another record.
I tried adding me.refresh right before "end sub" but that didnt work.
I tried adding "me.controlname.value = null" for every control on the form, and that didnt work either.
I also tried adding this code that i found on bytes.com:
On Error Resume Next
Dim ctl As Control For Each ctl In Me.Controls ctl.Value = Null Next
Property (used for look-up values) Service Type (used for look-up values) Rate Table (uses the Property Table and Service Type Table and is used as a rate look-up table for the daily service table noted below) Daily Service
Using the Daily Service table, I need to choose a property, choose a service type that is limited to the property (100's of service types, but a property may only have a limited number), and enter a quantity, and have the Rate Table return the specific cost of the service. I didn't think this was too hard in Access (2013), but I seem to be hitting a wall and spending too much time figuring it out. Now I am behind in my project.
I have been trying to do this in a table format, but have been unsuccessful in limiting the results of the Service Type combo box based on the property chosen.
I have a problem... I have four tables(but my problem is limited to just two) The tables are Orders, Customers, Items and OrderedItems The two more detailed below creates the problem when trying to copy an already existing order to a new since the customers usally order the samethings over and over again.
So I have made a copy button on the order form. The copy button should do this: 1. First save the old orders IDOrders and IDCustomers (which it does) 2. Create a new order with old data (which doesnt do) 3. Copy all post in OrderedItems with a new IDOrdered using INSERT INTO and using columns
My SQL statement is like follows: sSQL ="INSERT INTO Orders(IDCustomers, strSalesperson, strWhen, strSent, strComment) VALUES (" & itmpIDCustomers & ",'" & strSalesperson & "', '" & strWhen & "','" & strSent & "');" But the reply I got is "Could not find output table 'Orders'" but I have checked the names over and over again but it doesnt work.
I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:
The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.
The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.
The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.
The relationships are set up in the relationships window and referential integrity imposed.
Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).
If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
Hi All a newbie here so any help will be appreciated,
sorry for the long post but trying to give you all the information you might need.
I wrote a basic access database for my Church to aid in a paperwork audit for a charity food drop which we do monthly to give free food to the needy.
But each month it gets harder to find out who was in line first so I thought with all your help we may be able to randomize the names each month in a different order as to avoid confusion and also avoid people waiting in line as they turn up at 5am and we don't start until 9am.
So if this will work in access they can all come for 9am
I don't mind creating a new database and adding the additional information, if that's what it would take.
My Background I have created basic databases from scratch not using wizards, But I don't know much about code or how to implement it so any help in where code goes it would be very much appreciated.
Database details (Microsoft Access 2002 version)
Table Name = details Field name = ID (auto-generated) Field name = FirstName (text) Field name = Surname (text)
If possible it would be nice to keep a record of the randomized lists (in the database somewhere ?) each month in case anyone wants to see it or disputes the lists, where I can just create a report to show the details.
There will be approximately 90 to 125 names.
Thank you in advance for all your help in this matter
I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.
Implant Period >Treatment >>Exclude individual? >>>Individual ID
I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?
Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code: Private Sub cmdPreview_Click() On Error GoTo Err_Handler 'Purpose: Open the report filtered to the items selected in the list box. Dim varItem As Variant 'Selected items
I have created listbox with one column (contains one column only), now i would like to display all the characters of list item (want scroll bar to listbox).
How do i display all text of list item, I have already fixed Column Widths to max length (22";0.1"). However when scrolling to right, it is going to next blank column of list box, which is created only to change Column Widths property.
I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.
I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.
eg: keywords: look billy magic
list: "have a look and see" "spanish dave" "who is billy brag" "looky looky I go hooky" "who's the man from argentina" "could it be magic now"
my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".
the sheet has the keyword in each row and next to the column: =COUNTIF(list,CONCATENATE("*@",B13)) where "list" is the external data.
I created a form and created on it a list box which is a query that grabs certain number of fields from different tables. I would like the user to select from this list box of a choice and then store their selection into a table.This list box has three fields, but it needs to store the id rather than the item, the user would see the name of the item but the id of the item would be store into another table, called bid. It store all these three fields when a user selection one of the item from the list.
