Hey again.
Asked a question last time that might've been a little too complicated but I've made a lot of progress since then. :)
I've a form which I enter weekly data into which I have transferring into a temporary table. At the bottom of this form I've got a button which runs an update query which takes the entries from the temporary table and adds them to the main table.
(I'm doing a fantasy football league. An example is that if I enter that a player has recieved one yellow card in the weekly data, when I update the main table it will add one yellow card onto the total, etc.)
The problem I need help with is that I find that I need to get the temporary table to wipe itself as soon as I press the button to update the main table. Is there a way I can work it into the update query, or will it need to be done seperately?
Thanks.
(I have searched for this kind of thing, and found a topic that was dealing with VBA. From what was posted it didn't seem to relate to my problem, so I decided a new topic would be my best bet to get some help.)
I would like to create a "global" temp table that can be viewed by all users. The syntax is: Create [TEMPORARY] TABLE tablename (field type)
QUESTION1: When I put the optional [TEMPORARY] in, I get a syntax error... Create [TEMPORARY] TABLE t (ID int) I've tried... Create TABLE [t] (ID int) Is that the same as doing this??? Create TABLE t (ID int)
Can someone clear up my syntax?
QUESTION2: If i can create a temp table when does the table go away? When I close access? Do I have to delete it?
I have developed an Access database which produces financial reports based on a date range which the user specifys, taking the data from a SQL server. He chooses his dates and then runs a macro which creates the new tables, overwriting previous tables. Numerous financial reports run on these tables so I need to keep the table names the same.
The problem is that only one user can access the DB at anytime because the new user can't delete the other users table becuase it may be based on a different date range.
I'm open to suggestions but maybe what I need to so is create tables which are based on the user id when he logs in to the Access database so that he doesn't delete someone elses table. How can I do this please!!!???
For my inventory DB, when creating a purchase order, I need to store all transaction in a temporary folder and give a temporary number to PO and after finalisation only all data should be appended to main po table with new po number that will be last+1. And all data from temporary tables should be deleted. Like an air ticket is generated, first checks all details, give seat location also and if "Committed" gives the final ticket with no
Any idea about structural configuration and type of queries to be used
I want to store a query into a table, which I will delete later on. But somehow it shows me an error: Data type conversion error at the qdf = CreateTableDef assignment line.
Code: Public Sub LF_Query() Dim i As Integer Dim strSQL As String Dim qdf As TableDef
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
I'm trying to create a temp table, which is populated by a string, (taken from a recordset).
My problem is incorporating the String into the SQL statment, and making it work,
What I'm trying to do is to create a temp table, and populate it with the first record of the recordset, (which is an e-mail address).
(The recordset and the strings work fine). It's the SQL statement which doesn't work.
Here's the code I have :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ Set DBS = CurrentDb Set RST = DBS.OpenRecordset("SELECT Contact FROM Contacts_to_be_Mailed") Dim My_Count As Long Dim ContactString As String
RST.MoveFirst ContactString = RST(0)
Dim strTable As String strTable = "TempContact" DoCmd.RunSQL "INSERT * INTO " & strTable & " FROM ContactString "
I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited ) SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited FROM dbo_Billing AS b WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1)) ORDER BY Rnd(-(1000*b.indx)*Time());
Hopefully someone can point me in the right direction.
I have a small spare parts db which also has pictures of some of the parts. manufacturer partnumber wholesale retail picture
The form carries data on wholesale price which I would need to hide if the customer would like to see a picture of the required spare part. Is it possible to have a button to temporary make the wholesale field non-visible? Or would it be better to click on a button to open a new page with just the picture of the selected part and the retail price, if so what is the best way to achieve this.
I have been trying to create a login form that allows the user to change his/her temporary password logging it to the proper table along with timestamp and who done it info.But, after spending the morning trying to find the proper syntax I am flummoxed.
I can get everything to work accept the update of the fields. I can get the command to work (writes to the location) but it does the pop-up what is the parameter thing when it works. I have all the information just need to get it in so the command recognizes it.
DoCmd.SetWarnings False DoCmd.RunSQL "UPDATE lut_TeamList SET Pass = txt_Password.value WHERE TeamListID = Me.cbo_UserName.Value" DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedBy = Me.cbo_UserName.Value WHERE TeamListID = Me.cbo_UserName.Value" DoCmd.RunSQL "UPDATE lut_TeamList SET UpdatedWhen = Now() WHERE TeamListID = Me.cbo_UserName.Value DoCmd.SetWarnings True
Hello eveyone.. I have a slight problem which goes way over my knowledge of the subject!
I have to make a form saying "ISLAND HORSE RACING CLUB" appear for 5 seconds before loading up the main form to add records but I cannot for the life of me figure out how to do it!!
anyone got any pointers? had a look at "timed events" on this forum but dont know how to implement it to help me
I am using different values of a record (selected from a combobox) in my form/table which a user can edit and afterwards use for calculations and reports. the problem I encounter is that the fields are bound so if changed there is no undo if not mistaken.Is there a way to have for instance load a record in a temporary place (without making a large amount of temporary variables) and only update if the "save" button is pressed?
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.Looking online, I found this code:
Code: Public Function CreateRelation(primaryTableName As String, _ primaryFieldName As String, foreignTableName As String, _ foreignFieldName As String) As Boolean
[code]...
Even with the comments I don't really understand what exactly this code does or doesn't do.I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
- why it's not visible among other relashionships (or is it me?? maybe, it wasn't the code that was working but the query...) - how to implement referential integrity - what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?
frmUsedOilContract (contains a header and a subform) subfrmUsedOilContract (contains a few controls) [datasheet view]
- Removed Date - Voucher Number - Building Number
I implemented some code so that a temporary default value could be set for the date and the Voucher Number. I also have the default value for 'txtRemovalDate' set at 'Date()'.
PHP Code:
Private Sub txtVoucherNumber_AfterUpdate() 'Set current value to default value. txtVoucherNumber.DefaultValue = txtVoucherNumber.ValueEnd Sub
PHP Code:
Private Sub txtRemovalDate_AfterUpdate() 'Set current value to default value. txtRemovalDate.DefaultValue = txtRemovalDate.ValueEnd Sub
Also have code so the default value is null when the form is opened.
PHP Code:
Private Sub Form_Open(Cancel As Integer) 'Set Default Value properties to nothing. txtVoucherNumber.DefaultValue = vbNullString txtRemovalDate.DefaultValue = vbNullStringEnd Sub
This all works great until I try to change the date. If I use the default date (today's date), the new record will stay with today's date. If I change it to a different date, the new record displays a time instead.
I have a database split into a frontend and backend.
The front end has several make-table queries that create tables of data on which pivot tables and other analyses queries are based. The make-table queries get re-run every now and then as new data get entered.
The resulting tables are huge - 500K records in some cases. It seems like a bad idea to store them in either the front and back end.
Should I create a second "backend" that holds just these temporary tables? If so, what's the best way to design the make-table queries so they make the table in this "backend"?
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
I have two tables (Access 2010). One with a list of names (List1) and another with a very similar list of names (List2), but they differ in very small ways. For example, List1 might have John Smith, and List2 would have Smith, John L.; and Smith, John. List2 also has a unique ID associated with these names that I need to append to List1.
I need to design a form that will allow me to look up names in List1, and have it return all names that are similar in List2. I then need to be able to choose with record in List2 matches with the List1 entry (based on a few other columns in List2, such as birth date) and have the form add that unique ID to the List1 record.
