I want to save the timestamp created in a table when a user opens the database. My vba saves the computer name and user name and sets the currentuser = yes. The table is mysql odbc, and has the requisite timestamp and id fields. I have a form that I can open that shows who is online at that moment. When a user closes the database, the currentuser field is set =no. All of that works well. Now, I have added a "starttime" field in which I would like to store the timestamp that was set upon opening, since when a user logs off, the timestamp gets changed to the log off time.
Code:
Dim myQry As String
Dim sUserName As String
Dim sUserComputer As String
Dim currentVersion As Date
[code]....
The UserTimestamp in red is the field value of the first record in the table, not the usertimestamp in the user's record. How do I store the log on timestamp in the "starttime" field?
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code: Dim strFile As String Dim temp As String Dim tbl As String Dim db As DAO.Database
I have a database with two tables, and a form that is bound to the 1st table. I have a duplicate record button on the form. One of the functions I want to happen is when the duplicate button is pressed, one value is copied from the form and added to the 2nd table. The tables are basically set up the same with an ID column which is the key and an autonumber, and the second value is a number. So I want to copy the number to the other table and add a record. What is the best method? Can I use an insert into and just copy from a variable, or is the better method with a recordset. Either way, I have not been able to figure this silly thing out and it is preventing me from moving forward with this database.
I am trying to copy a record as new record in vba in access so i make a button for the user so that they can copy a record each time and change a certain field if they wanted. How would i do that.
I have four tables: tblinvoice / tblinvparts tblquotes / tblquoteparts
when I open the tblinvoice form it has tblinvparts as a subform. tblinvoice has a listbox that lists quote numbers linked to vehiclereg. I have a button on the tblinvoice that will open a popup tblquoteparts continuous form linked to the quoteid on tblinvoice. These filtered records have a select button so I can select all or some.
I have now got stuck as I need a button on tblquoteparts popup that will copy the selected records from tblquoteparts and paste them into tblinvparts where quote (on tblequoteparts)=quoteid in tblinvoice.
In a nutshell, I would like to copy selected records from tblquoteparts to tblinvparts.I need to duplicate the records because only 10% of invoices are generated from a quote and the quote parts/prices may differ from the final invoice.
I've got some code that loops through a record and copies the non-null fields to another record. This is the partial code:
For Each fld In tdf.Fields If Not IsNull(Forms!contact_lookup![Contact_sub subform1].Form(fld.NAME)) Then strSql = "UPDATE tbl1 SET tbl1.[" & fld.NAME & "] = '" & Forms!contact_lookup![Contact_sub subform1].Form(fld.NAME) & "' " & _ "WHERE tbl1.[FC_APN] = '" & Me.txtApn & "';" DoCmd.RunSQL strSql End If Next fld
This works fine until it encounters a field whose contents has a reserved character, like a single quote (" ' ") in it, for example, "What's the what." At that point the SQL thinks there's a " ' " missing in the statement, and I get an error 3075 (syntax error: missing operator).
The GetDates Command BUTTON should generate the red data above, like:
Private Sub GetDates_Click()
Go to a new record Go to the field App_Freq and fill it with: Copy/paste the data from the field of the same name in the previous record Go to the field App_Date and fill it with: data based on this calculation using the PREVIOUS record fields: App_Date + App_Freq (which are days) Go to the field Action_Date and fill it with: data based on this calculation: App_Date (of current record now filled out - step above), minus 7 (days). End Sub
That's it!
All the dates format is set to medium date. An error message should come up in case App_Freq is empty, for example
Also note that the data can be manually changed at any time and should not revert to its last calculation. The button only generate data into a new record.
I have a projects Database with: Projects_Table, Staff_Table.
Both tables has forms for data entry or update. Each project has many staff allocated to it.
In the Staff_Form I placed the Project_Code in the form heading section. The form opens based on a search criterion on the project_code so it shows the staff for that project only. The issue is when I try to add a new staff for that project on the Staff_Form, the project_code stays blank in the staff_table.
What I need is that the project_code in the staff_table for that newly added person to have the same project_code on the opened form. Simply to link the staff to the project they work in when I add them using the form.
I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself.
I have two tables in my Access database, their fields are exactly the same (for now). One table is called Uncheched. The other one is called Checked. So what is need is a macro that takes selected records in table Unchecked and copies them to the end of table Checked. Actually CUTS from Unchecked and PASTES to the end of Checked table.
Sub MoveRecords() DoCmd.RunCommand acCmdCut DoCmd.OpenTable "tblChecked", acNormal, acEdit DoCmd.GoToRecord , , acLast DoCmd.RunCommand acCmdPaste End Sub
I am using Access 2010.Most of the time a tenant pays his exact rent. When that happens, I currently type in the payment (taken form the Rent field) and date of payment (current date) into a form. The date should not change.
I would like to place a checkbox into the form. If checked, the rent would be entered into the payment field and payment date (current date) would be entered automatically. Otherwise, I would just enter in the payment and date manually.
I have a field called Shipdate. I have created a new field that is called invoicedate. What I want to do is copy all of the shipdates and paste them in the invoicedate field for my old records. From here on out they would be distinct fields but for old records and reporting purposes I need those dates.
I need to make changes to a table already containing data. Currently the autonumber field is simply there as a primary key, but I now wish to use it for another number field (Accession No) which was previously completed manually.
As this doesn't seem possible or practical (as I need the flexibility to overwrite the number sometimes), I just want the autonumber to also appear in the Accesion No field when a new record is added (but be manually overwriteable).
I've had a play with default values and lookups, but with no success, and haven't found anything on the forum. Where am I going wrong, or is this just not possible?
I'm using the DoCmd.CopyObject method to copy a table from my front end db to my back end. My back end db is password protected and it keeps prompting me for the password. Can I somehow send the password in code so that the user is not prompted?
Two Solutions to address moving an Access Memo field into Excel when string has > 255 characters. All my 'reports' use Excel VBA (Access Reports are not used). The Excel reports can have 40,000 records. Speed to create the report can be an issue.
Describing 2 Solutions below to address moving Access memo fields with > 255 characters into Excel.After running this code
Code: 720 ObjXL.DisplayAlerts = False ObjXL.Columns("X:X").Select ObjXL.Selection.NumberFormat = "@" ' set column to Text 730 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsNutsAndBolts
The Comments column are limited to 255 characters. So, the CopyFromRecordset (recordsetvariable) creates the 255 character limitation.
The reason? The 255 character limit is because CopyFromRecordset sutomatically uses the Characters property of the Range object. The 255 limit would not be there if the Cell Value property is used to assign the string to that cell.
Dim sRx as String ' String Prescription sRx = "String with > 255 characters ... you fill in the rest ...." Cells(1, 1).Value = sRx ' Cell's Value property assignment can be very large
Solution 1:
The record set is still in memory. By using a loop, a cursor can start with record 1 (memo column) and assign that value to the Excel row / column using the .value as shown above. Basically, this moves one memo field at a time, record by record. e.g. Read First recordset in Access, copy to variable, assign value to Excel row/column Then move next on each Access and Excel.
Solution 2:
An Access Memo filed [RxNotes] can have up to 750 characters. Cut it apart into three new fields that end up out in the very right Excel columns AA, AB, AC.
Note1=Mid([RxNotes],1,250) Note2=Mid([RxNotes],251,250) Note3=Mid([RxNotes],501,250) Then using Excel Object - Concat the cells back cell by cell... X2=CONCATENATE(AA2,AB2,AC2))
Then delete the columns AA, AB, AC to hide the evidence..Neither solution is all that elequent. Read about this and by golly, it made a difference
ConcatComments = "'" & CommentString
Before using the CopyFromRecordset be sure to add a single quote in front of the large string.
Turns out the interface between Access and Excel look for this to prepare Excel immediately for the string to be a string, not something else. Some of my strings had weird print characters that kind of looked like Japenese characters. It seemed random, it always happened if the string was 255 or more characters (ramdonly, not always). The single quote doesn't show up in Excel, but got rid of all the noise.
I have two tables and one of them (table1) has two fields called pct_id and pct_name. table2 has the field pct_id.
I also created a new field in table2 called pct_name. What i'd like to do is set table2.pct_name=table1.pct_name wherever table1.pct_id=table2.pct_id automatically.
Is there a query that can check to see if its the same and update table2?
Heres what i tried but it didnt work
UPDATE table1 SET table1.pct_name=table2.pct_name WHERE ((table1.pct_id)=(table2.pct_id));
I am using MS Access 2010 and I am trying to figure out how to copy a field(s) to another like record. For example I have simple split-form that contains detail items purchased (product number, item purchased, cost, etc). I have a check box call "chkApproved" and date box called "Approved Date". The user have a choice to select the invoice number, once selected the invoice number (there maybe anywhere from one or 2 to 4 rows based from the same invoice number).
The manager will go to the first record and check the "chkApproved" and date is automatically updated as today's date (Approved Date). This is fine if there is only one row, but when you have the same invoice number with 2 to 4 rows the user has to go to each row and check the box. I want to somehow automatically populate the rest of the rows within the same invoice number I don't need to create a new record only updating the existing records.
I have prepared a table in a database which includes a yes no box to one of the fields.
The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.
I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table.
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.
If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.
The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.
Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.
I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code: Private Sub CommandButton1_Click() ChooseFile End Sub Sub ChooseFile() Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" . 3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn" 4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete" 5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .
I need some simple code that will copy an Excel file or a table in Access to a specific location on an FTP server. I would think this would be a very simple task, but I have yet to find any sample code that is *simple*. I have seen lots of code that requires downloading this dll or that mda, but the examples don't work. There must be something built into MS Access 2010 that will allow a file to be uploaded to an FTP site.
All the variables are known:
The FTP location (it never changes) The FTP Username and Password (they never change) The destination folder on the FTP site (it never changes) The File type (it never changes) The File name (available from the form in Access from which this will be executed)
I can either produce an output file, then copy it to the FTP site, or I can export the table directly to the FTP site with the file name for that day.
This seems to be a very simple task with no simple solution. Currently I am using an FTP app to get the file to the FTP site, but I would like to automate this. The process that creates the output file is already automated, so I would just like to add this to the existing code as its own module.
just made all that above up and none of it is a real function/command in VBA, but is just the kind of thing I'm looking for.
I would think that since I can download and XML file from an FTP site that it should be child's play to upload a simple file to an FTP site, but I can't figure it out.
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
I’d like some assistance on the following if possible:
I have a database where I want to update a field (Agree_ID) based on the data input in a second field (Pond_ID). Agree_ID uses only a portion of Pond_ID. I’d like some routine (macro or VBA code) to assist.
Example: Pond_ID – keyin "05-04-1P" Agree_ID – updates to "05-04"
I used “=Mid([Pond_ID],1,5)” to update Agree_ID which appears to work. However, I have to close the record; run a macro that runs the code; then go back into the form and open the record. I’d like this to happen automatically when I keyin the data in Pond_ID.
Any ideas would be greatly appreciated. Thanks, SKK
