I've been running into this problem in Access. I have records which have more than 255 fields. The easiest solution would have been to get a table with more than 255 fields, but I can't do this, so I've had to split up the tables. The next easiest solution would be if there is a way to link the tables--I have a data field "IntAccNum" identified as the primary key in each table--so that when a new row is formed in one, it is formed in the others. This wouldn't waste space, since any row in the first table would necessarily need a corresponding row in other tables. Is there a way to do this?
If not...
The problem I run into is when I populate a row in the demographic table and then try to fill a corresponding row in another table. I use a command button to for a macro to "OpenForm", and then try to "SetValue" of the appropriate field in the second table. This adds a new row in the second table. This does let me populate the correct row of the second table the first time I try for each row. However, after this initial population, when I switch back and forth between the first and second table, I get an error in access since obviously I can't keep adding a primary key with the same value in the second table.
Do I need to do some sort of query to see if that row exists in the second table already. Or is there some simple solution to what must be a very common situation that I am overlooking?
Hi just started working for a Company and I found they have tons of databases but no structure or standard, so I would like to build a tool that will go thrue a list of databases and get all the table names, the items and type. Is there functions in vba that can get me a list of the tables in the database and the get all the items in the database ?
I have items, but some of them have multiple names for one item. Is there any way to tell Ms access that that item has those other names? I have 5 fields, named "Model" "2nd Model Names" "3rd Model Names" "4th Model Names" and "5th Model Names."
So I've been reviewing cascading combo boxes and I am finding some good stuff. But here is a preliminary step I need. I need to tag each record with something like what month are they assigned in, and what products. Then I need the combo box to display what that month is that they are tagged in, and then populate the second combo box. It is the second combo box that will show the record on the form. Its possible that there will be two items they are tagged with.
I have a form with a combo box named 'Venue'. The combo box values comes from a Table with a list of about 200 countries. As of now a user is able to select only one country. However, I want users to be able to select more than one country.
I'm having an issue with pound signs in my table for specific items. I've create a DB that when you click a command button it opens a form and in the form it has a notes field. In the notes field once where i had data is now pound signs.
The DB is setup so when you click another command button to close out, it saves the record. It works 98% of the time but the i'm concern about the other 2% of the time.
I have a table with inventory items, a separate table with storage charges per day (ex .03, .04, .05 per day/per item)
I have created some queries where I take the items & # of days they have been in storage and when I try and create an expression for storage charges based upon QTY & # of days I am getting results like it is multiplying whole numbers and not very small increments like .03
I have checked the math, and its not multiplying by 3 instead of .03... I cant quite figure out how it is coming up with the numbers.
Again, the pricing is coming from a lookup wizard to another table. It seems like it should be a very straight forward expression but I cannot get it to work. Does the figures being from a lookup have any issues?
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K and 5 pc set = A,B,C,D,F and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000 B=3000 C=3000 D=3000 F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
MsgBox "Booking successfully added!", vbInformation 'Depending on which month is booked, will determine which listbox is goes in on frmMain If Month(Me.Date_1) = 1 Then Forms!frmCurrentBookings!ListJ.AddItem = Me.BoI 'Add to ListJ on frmCurrentBookings ElseIf Month(Me.Date_1) = 2 Then Forms!frmCurrentBookings!ListF.AddItem = Me.BoI 'Add to ListF on frmCurrentBookings End If
End Sub
So basically... I have a date field in the form 'frmAddBooking'... if the month of the date is 1 (January), then the conntents of the date field is added to a listbox in a different form (frmCurrentBookings)... but this doesn't work...
If somebody could help me I'd appreciate it greatly.
Up until recently, when I selected multiple objects in design view (on a form) and right-clicked the mouse, I had several options in the 'Size' sub-menu (including 'Size to Widest / Tallest / Narrowest / Shortest').
Then I tried to be clever(!) and create a new custom toolbar which had just these options on it, as I use them frequently. This was all very well until now, when I can't find the custom toolbar I created, and the options have disappeared from the Size submenu aswell!
Has anyone got any ideas on how I can get them back??
(I have tried going into Customize to drag them back, but they're not there either! :( )
I have created a switchboard that has 8 more sub switchboards. They have got items from 1 to 8. My database is setup with users and their authorised access levels. Admin has level 1, Manager level 2, Users Level3. Now what i want is that when admin logs in all the items in the switchboard must be available, but when a Manager with leve 2 or a user with level 3 logs in to the database then certain items on a specific Switchboard must be diabled For example lets say Sub Switchboard No 3 and its item number 4 (SwitchboardID = 3 and Item Number 4) should be disabled. I tried searching it on the Forum but no use. All i request you is to help me in this issue.
I am trying to learn to use MS access 2007 and have a query about copying and pasting.
Is it possible to paste the same line of text into multiple lines or do I have to arrow down and paste into each individual box?? I can highlight any number of boxes in the same column but when I click paste it only pastes into the first highlighted box.
why do ALL form items disappear if certain records are not there? my mainform is fed by a people Qry and the people have payments however, if you delete the payments, all of the items on the mainform disappear. if i go into the table and manually add a payment, everything re-appears. the mainform is exclusive to "peopleQry" and there are 2 subforms which show different data at different times. when the form loads, these subforms have a sourceobject set to "" so it couldnt be them. the mainform doesnt display any payment information unless asked and then, it is shown on a different form. this is a bit of a problem now.
I have to redo the company I work for’s insurance program and they want it done in access. I’ve hit a big snag though. Not only are my Access skills very rusty. But also I have a very complicated task here! :)
I would need a customer table an insurer table and an adjuster table. These tables would link to a Claim table. In the claim table I need to make it show the items that the person is going to get with there insurance money by getting details from a table with all of the information for the items.
But how do I make it so you can add more then one item to the claim?
Some people may have 1 item to replace some people may have 20 items.
I am trying to enter multiple parts into the same field and using commas to separate them. Is there a way to create multiple records with the same information, but change the part numbers in them according to comma's?
Would look like this.
Primary Key Location Part number 789 bin89756 89756,87564,78965,12565,45896
I want it to do this in the end.
Primary Key Location Part number 789 bin89756 89756 790 bin89756 87564 791 bin89756 78965 792 bin89756 12565 793 bin89756 45896
I have a query which is based on a tbl which has data on chemicals in storage. The query is based on two tbls as follows; Main tbl ProducdID ChemicalName
Second tbl Batch number TransactionID Transaction date Purchased check box Used check box, expirydate ProducdID
When a chemical is purchased a tick goes in the Purchased check box with the other relavant data. When a chemical is used a tick goes in the Used box along with other relevant data in the other fields. In essence each chemical will have only two records 1. a purchased record and 2.eventually a Used record.
I want the query to report on all chemical records which are still in stock. So it's not as simple as adding a True or False to either check boxes. Showing records which have ticks in the Purchased CB ,or not showing records with ticks in the Used CB, will still show a Chemical which has been Purchased but may have been used at a later date and is no longer in stock.
Any suggestions? I hope I haven't confused anyone with my problem?
Can anyone help? I have a table with the following: Booked with a y/n field Person Name with a text field So... Yes(True) Jim Yes(True) Jim No(False) Jim
What I need is to be able to show a query that shows if the checkbox is ticked (True) and the Name is Jim total up how many true items there are. So from the above I can see that Jim has 2 true items. I would then like this shown in a text box is this possible? Also, it is not always going to be a person called Jim so... (From above) Yes(True) Jim Yes(True) Jim No(False) Jim Yes(True) Jack
So how would this go on a query in a sub form? So Name Jim would show 2 and Jack would show 1? At the moment is is grouping the name and I dont want that. Any ideas Cheers
Hello all, I am new to this forum. I have this problem where I need to sort the 4 books in the tables below according to the highest quantity of book sold.In this scenario, Book C would be 1st in the table with a total quantity of 8. How do I write a query in access to perform such operation. I have to use DISTINCT to get each book and also count the quantity(e.g. Book C:5+8). But it seems that I can't use Count and distinct together in Access.
I'm building an order entry database. It has two tables (amongst others) called tblOrders and tblOrderDetails, related together on the OrderID field. For every order, there is one record in tblOrder, and as many records in tblOrderDetails as there are individual lines in the order (so, if, for example, the order is for 10 pencils and 2 pens, then tblOrderDetails has 2 records).
Each entry in tblOrderDetails has a Status field, which indcates whether or not the items have are in manufacture, shipped, delivered, etc).
I need a way to get Access to show me only "Open" orders (i.e. ones in which not every item has been shipped). Can I set up a query to determine how many lines each order consists of, and then is there a way to get access to check if all of these are "Shipped"?
I have a form that users type in a part number and a due date. I then run a report that prints the part number, the components needed and the processes needed. For example:
------User Form-------
Part 1 6/25/05 Part 2 6/28/05 Part 3 7/1/05
---------Report--------
Part 1 6/25/05
Component 1--------SAW-----WELD------SMOOTH Component 2--------WELD-----PAINT ------------------------------------------------------------- Part 2 6/28/05
Component 1--------DRILL-------WELD Component 2--------SMOOTH-----PAINT ------------------------------------------------------------- Part 3 7/1/05
I'm confused about how to go about this. I don't want the users to have to manually type in anything. I want the components and processes to show up when they type in the part number. I want the user to be able to check off processes as they go, but I want all the processes to show up when they type a new part in the form. I would appreciate it if someone would point me in the right direction.
