I am trying to make a database for a shop which can do several things:
1) I want to give in what is being bought by the shop to make their products form. This has to be saved in a table named STOCK.
2) I want to be able if I say in a form that from these things I made that product that these things dissapear from the STOCK table and that the final product is added to the STOCK.
3) If the final product is solled I want to remove it from stock.
In the end if I want to see what is still in the stock I will only have the things that are still there because the rest is removed.
I have a form with only 2 fields. One field is a drop down (Yes, No, N/A). The other field reads the first field and then assigns a value using an IIF statement (If No, then 0, otherwise 1).
I want to run a query on this data, but when I do, the 2nd field's result doesn't appear in the query (nor the main table).
How do I save/record the result from the 2nd field in the form to the table?
What I'm trying to do is create a database system for my company, it's not overally complex in the sense it's just managing the estimates and invoices we do.
Now I've tried to break the data down as sensibly as possible.
What i'm having trouble with is I have created a autofill combo box to fill out text boxes with information from the Clients table, the idea is that the client can be added via the Estimate form or pulled up from the record depending on the situation. Now the problem I have is one;
I cannot enter data via the form
and two
When you flick through the forms only the combobox information is staying put, either the text field is empty or incorrect.
I'm pretty it's to do with [Data Source] but I can't seem to get it to work. How to make it possible to pull up clients when typing an estimate OR be able to add a new client via the same fields. Secondly, when the Form is review the autofill text field display the clients details.
I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
I have a built a database to record and collate info about clinical trials that are run within my department. I have one large table [Trial Info] which contains all trial info and have created a second and third table [Milestones] & [Comments] to collect dates of various milestones and a notes that occur throughout the course of a study.
The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.
All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.
I have setup the relationships between tables as best I can.
The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.
The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.
This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record
Quote:
You cannot add or change a record because a related record is require in table 'Trial Info'
How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?
:confused: I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.
Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible
Sub delete() DoCmd.DeleteObject acTable, "060204_cfd_postings" End Sub
In my program you are allowed to make backups of the current databases to an external file. It also allowes you to get the tables back from one of those files. At least it should.
I thought this wouldn't be a biggy. I thought I'd just delete my current tables and replace them with "docmd.tranferdatabase" acImport.
But the stupid thing wont let me delete its tables because they are connected to each other. Can I somehow delete the link before deleting them?
If so will the links be there again once I restore the tables from the backup? Or do I need to reconnect them somehow to each other.
Or maybe another approach would be to import them without deleting the original ones. Thus the new ones get the same name of the original ones + "1" or something. Than I delete all records from all my original tables. And refill them with all the records from the new ones (the + "1"-ones) And delete the new ones. And if I would do that, would it be best to use AcImport or AcLink (from docmd.tranferdatabse)
I was wondering if there is a way in selecting all tables and deleting at one go instead of selecting one by one. Perhaps a little bit of programming. Tx in adv
Having searched the last couple of days, I am unable to find a resolution to this problem. If it has been answered already, a simply link or thread name would be appreciated. If not, help is greatly appreciated.
I have code running that imports data from a .txt file into a table.however, on some days there will be errors importing some of the data. I have found that from the last three months, there has been no data of consequence lost so, I don't care about the data errors. However, any time there is an error with the import it creates a table that is named for example:
01-03-05 Auth_ImportErrors
where "01-03-05 Auth" is the name of the file imported. So each time they import a file, a new table is created. Is there any code that I can run that can automatically delete these tables upon exit?
I routinely import from an Excel document provided by a third party. Most data in it is ok, but 10-15 records always end up in the import error tables which Access creates automatically when formats do not match. I am trying to automate this whole process thru VBA and am pretty successful so far, except that I haven't figured out how to programatically get rid of these tables (I do not need them, and data imports anyway). Is there a way to destroy a table programatically in Access?
i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.
I'm running MS Access 2007. I'm creating an import procedure (for 2003 and 2007) that will automatically import an EXCEL file into a table for MS Access 2003 and 2007. The automatic import procedure works fine, but everytime the procedure is ran it creates an 'ImportError' table, due to blank rows (i.e. 'Type Conversion Failure'). The blank rows are due to the source EXCEL file not having value in a cells. This is normal for the source EXCEL file. These 'ImportError' tables are numbered each time this procedure is ran. I added a 'DoCmd' (see below for statement) statement in the procedure delete these tables, The below statements are used in a loop. I see that there are several procedures out there that use 'DAO' to accomplish this. If possible, I would like to use something that will use the below 'DoCmd' statements. This will eliminate the need to touch each PC to set 'ADO' or 'DAO' on.
My table (excel spreadsheet) sits on another directory from the DB. If I delete the table on this directory and immediately replace it with a table with the same filename, format, etc (only the data has changed) will each database user have to relink the table on their desktop DB? Or will the forms/subforms/reports still maintain their relationships/functions and just display the new data?
I have a number of linked tables that vary depending on use. How to delete them or drop them all from the database? I produced/stole some code that deleted all tables - but it didn't delete the linked ones.
I have a crosstab table that is displaying in a form within a form because the form has data coming from multiple tables.
Crosstab Query looks like Name Sum Cust1 Cust2 Cust3..... Jon 50 250 Bill 100 50 100 Kevin 200 50 100 Ryan 10 500
The issue are the blanks in the rows for each Name. When I bring it up on the form, I want the columns to be the Customers but only customers with amounts. The original form is there to show the name and position and the form within the form should have 1 row for name of the person that is being selected and columns of customers WITH A BALANCE.
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
Embarrasing question, but my serches have proved fruitless.
Saving a Date field (Short Date) is saving as 1/02/2008 or 27/02/2008 in the table. Formatting this data in a query as dd/mm/yyyy displays correctly, but the mid function mid([Date],4,2) still picks up the table format, not the query format
My objective is to extract the 02 (month) using the mid function in a query. Beacuase the length of the day component varies from 1 to 2 characters, this is proving impossible
Is there a way to save the date as 02/02/2008 in the table?
Tried using input mask dd/mm/yyyy but kept getting invalid format error
I want to be but to save a space in a field on a table but every time I do it appears to be a NULL and therefore fails a validation rule that I wish to preserve.
Access 2003. I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table
I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula
="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source
What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.
I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.
I have tried the beforeUpdate method within the ItemNo textbox
Private Sub ItemNo_BeforeUpdate(Cancel As Integer) Me!ItemNo = Me!ItemCalc End Sub
but nothing happens (in that the table is not updated)
Any way of putting together an incremental item number based on the selected category with a "TV" prefix....
I am trying to create a saved export on a table i have. However i want to filter so when i do the saved export it exports the table with the filtered result only.However when i filter my table and export it and save the export. The first export comes out with the filtered results but when i go to my list of saved exports and export it again, it doesnt come filtered and its just an export of the whole table.
