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.
View RepliesIt is a continuous subform (Names: Form = ClientUpdate / Subform = ClientUpdateSub.
All of the data implicated here is direct and in the subform's query table including the button we want to program.
(This is my example but it wont stay in columns in this "post box" )
IDNo App _ID App_Freq App_Date GetDates Action_Date
22 18 56 21-Jun-13 BUTTON 14-Jun-13
21 19 56 16-Aug-13 BUTTON 9-Aug-13
*
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 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
Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
ok so I have two forms open: One is a SP Bookings form with a subform SPDetails Query subform. The other form is the not connected/related SP Contacts Query form, to which users enter Full Name weight age ect. Need to copy the names of the new customers Full Name to the Lookup Contact Column in SPDetails Query subform. I got this far, with my trial runs are commented out. The only thing it does when I click on the button is move to new record on Sp Contacts Query Full Name and Copy the previous record. How do I get the button to copy the Full Name from form SPContacts Query.Full Name to the SPBooking Query.subform SPDetails Query Subform.LookupContact
Code:
Private Sub Command52_Click()
Me.Full_Name.SetFocus
DoCmd.RunCommand acCmdSelectRecord
[Code].....
I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:
Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _
[Code]....
While the validation runs a boolean keeps track of validated input and errored input.
After validation the validated input is dumped in the table.
Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.
The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"
I am trying to copy a subform record set from one record to the next record. The data in the main table is copied over using this method;
Code:
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
v1 = Me![Today's Date].Value
v2 = Me!Insured.Value
[Code] ....
Can I integrate the copying of the subform data to the new record with this or do I need something different, and if so what?
It's a many to many relationship and I've tried adding the following line to the code:
v5=Me![Endorsements Umb XS].[UmbID]
same=v5
and it works but it only copies the first record out of the set.
Im using the UPDATE Query in Access
UPDATE MAT_Clean SET FCODE = 'BC', NOTES = 'HC RAMP'
WHERE FCODE='BC HC RAMP';
Instead of typing what needs to go into NOTES everytime, how can I specify whatever comes after 'BC' to go into NOTES? Use 'BC *'?
Thanks!
Its been a few years since I used Access, and despite searches I cant find what I'm looking for. I have a VERY simple form, with only one record on it - "address". I have some print buttons which will print that address to different size labels, and some navigation items, but only one actual record - which is "address" (in the table, this address is a MEMO)
I would like to have a command button that when clicked copies the current record, and opens an existing Excel Spreadsheet and pastes that address into (for example) cell C8
Can it be done easily? Is there a DoCmd.TransferSpreadsheet kind of secnario that works with current record only?
I have a form which has been in use for some time now, and works extremely well for all users; as part of a recent update to that form, I added a few events to various controls; most of them are message box prompts to remind the user to do something, but one is automatic entry of text into a memo field when another field is updated, and it is really this last one that is causing an issue.
There is a command button on the form which has an embedded macro, created using the wizard - it is probably the last, or one of the last few buttons that have a macro as opposed to VBA.
Essentially, when this button is clicked, I would like a duplicate of the current record to be made (thus giving it an Autonumber ID of its own), and if possible I would like this to be done without triggering any of the after update events on the form. Is there a way to disable these events and copy a record in its entirety, re-enabling the events at the end?
I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated. I have 100 other tables to be related with referential integrity(+update&delete) to Master for both fields. Apart from Job No and Revision No, all 100 tables have different set of fields which is why I had to come with so many tables.
Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship. So, I created 5 other SubMaster1, SubMaster2, ...., SubMaster5 which are related to Master with relationship with referential integrity (+update&delete). Then I assigned 20 tables to each SubMaster so that 20 tables are related to each SubMaster table.
Whenever I create new record in Unit, the new record is generated in each SubMaster using update query for each SubMaster table. I have all the forms and necessary query laid out. The only missing part is being able to duplicate a record. I have limited knowledge in VBA, but I should be able to modify it to address to my requirement.
I want to copy a given record in Master, SubMasters and 100 tables as a new record. I need this feature so that I can select certain Job No and Revision No and copy that as a new Job No(assigned manually in a form) and 0 as the revision number. Possibly a button which will ask for new job number and copy everything from the active Job No and Revision No to a New Job No and "0" Revision No. The existing record may not be there on all 100 tables for the given Job No and Revision No. If it is there, then copy otherwise ignore for each of the tables.
I have a table "ItemList" which lists all the unique name of the 100 tables.
Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?
I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.
Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week.
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 main form that has some sub forms on it linked by master child on PK. When I select a new record on the sub forms I want to take a date value from the main form and populate the value into a field in the sub form. How do I do this as I cannot see a new record event on the sub form?
help would be much appreciated.
regards
Peter
I have an access form that needs filling in daily by various people.
It's to document changes to a website and I currently have a combobox box set up for the various sections to state whether they are AMENDS, REVERTS or NO CHANGE.
I have set conditional formatting to then highlight these sections but am also trying to get it to work so that if the user chooses "NO CHANGE" then the data for that field copies over from the previous record.
I have set this up in the AfterUpdate code for the combobox, but nothing is happening, not even an error....
Code:
Private Sub COMBOBOX1_AfterUpdate()
If Me.COMBOBOX1 = 3 Then
Me.[FIELD_TO_CHANGE] = DLookup("[FIELD_TO_CHANGE]", "tb_TABLE", "[ID]=Forms![form_FORM]![ID]-1")
End If
End Sub
(Where 3 is the value of NO CHANGE in the combobox, and FIELD_TO_CHANGE, tb_TABLE and form_FORM being the names of the various elements)...
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).
As I proceed with my development I continue to rename fields. The effect of those renames is unclear to me. They seem to effect some things and not others.
What rules do I need to know about renaming the fields in my database and the effect on the forms I'm working on.
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.
FIELDS
Rent
Payment
PaymentDate
on form chkPayment
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'm setting up a college database. On my enrollment form i have the following fields
Enrolment_ID
Student_ID
Course_ID
I'm receiving the error in my Student_ID field. It's telling me a related record is required in my Student Table.
The drop down shows the student name/date of birth/Education number
But if i set it to just show the ID it works fine, is there a work around for this as it will make life a lot easier for the dropdown to display the student names rather than the ID?
The dropdown is based on a query i have of accepted students....
I have a main form which does nothing except filter subforms through a cbo.
On the main form are two subforms. One shows top line data, the other shows a breakdown of the top line data, and are linked by an unbound textbox (it's how it works, and does so perfectly)
I'm running an append query to duplicate a record in the second form using vba/sql BUT... need to have one of the fields values changed based on a field on the parent table.
If Forms!frmmain!frmPost.Form.RecordsetClone.RecordCo unt > 0 Then
strSql = "INSERT INTO [tblposts] ( TopLineID, AccountID, TransDate, Cat, SubCat, Debit, Credit ) " & _
"SELECT " & lngID & " As NewID, AccountID, TransDate, Cat, SubCat, Credit, Debit " & _
"FROM [tblposts] WHERE TopLineID = " & Me.TopLineID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
Credit and Debits are reversed as I want one to zero out the other.
In regards to the AccountID, I've tried allsorts and it's just not working... to the point I'm almost giving up and finding an alternative.
Whats the "Correct" syntax to attach a "WHERE" statement to the highlighted [AccountID]'s (which needs to be the value on AccountID on the Parent table)
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
[Code] ....
I am trying to build a function that will create a dynamic query for a chart on a Subreport.I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.
I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. [URL] .....
Code:
Option Compare Database
Option Explicit
[code]...
how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.
in a continuous form i want to click on one record and have the one field change the background colour to highlight it. When I use the code: Field. BackColor = vbYellow it changes the background on all the records. Is there a code to say only for the record with focus?
View 1 Replies View RelatedI'm using Access 2010. I'm passing a string into the OpenArgs of my report - works fine. In the report there are 3 rich text fields which may contain the text I passed in, and if so I want to change the color of that text to red so it stands out.
The value passed to the report changes so I'll need to use VBA in the detail's format section to check each of the 3 rich text fields.
I wish to change the backcolor of a field on a current record being dispalyed in a 'multiple items' form. However when using me.A.backcolor = makes refrence to all fields called A on the 'multiple items' form and therefore all fields change colour not just the current record.
View 2 Replies View RelatedI have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
Code:
txtCost = DLookup(cboRate.Value, "tblItem", "ItemID=" & ItemID)