I get an error for this code: this is the error, i can firgure out how to have the string not be the textbox, but sub in the texbox name.
Code:
myFormstr = Array("F_Value1", "F_Value2", "F_Value3", "F_Value4", "F_Value", "F_Value6", "F_Value7")
For Each mystr In myFormstr
me.cboProduct = Me.mystr
Next mystr
I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"
I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.
I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.
Can someone show me the syntax for this.
Thanks!
I have several buttons on a form and I want to set enabled = false on one of them when I open the form - that's fine
I have two txt fields - ReceiptNo and BankPaymtId if the both have a value I want to make the button enabled = True
This is the code I have used on the BankPaymentId On Dirty event
Private Sub BankPaymentId_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
This is the one for ReceiptNum on Dirty event
Private Sub ReceiptNum_Dirty(Cancel As Integer)
If Me.[ReceiptNo] <> "" Then
If Me.[BankPaymtId] <> "" Then
Me.CreateOutlay.Enabled = True
End If
End If
End Sub
Nothing seems to be happening.
Even the Form on Dirty is not working now....
I'm trying to update the account number via an input box with a do loop but I don't have the code right. The at the input box, the user will type in a 4-8 digit number and it will update all the account number fields in a table that are null.
View 1 Replies View RelatedHi. I have two forms that both look at the same table. One form is for inputting data and the second for is for closing the job. I want to create a button that when clicked automatically changes a field on the input form and also fills in the current date on a field displayed on the closeout form. I then want it exit my two forms and take me back to my switchboard. Can anyone out there help?..Thanks.
View 6 Replies View RelatedI want to be able to update a date field in my form with a command button based off the date the person picks off a combo box in my header... is this possible?
View 1 Replies View RelatedI am setting up a "to_do" list in access. One of the field is the date the task was last completed. I want a button to update that date to the current date for the record that i am viewing on the form. Below is the code that I am using. When I click on my button, it updates the first record in my to-do table but not the current record displayed in my form.
Private Sub Cmd_Update_Click()
Dim DB As Database
Dim rs As Recordset
Dim i As Long
On Error GoTo Command4_Click_Error
Set DB = CurrentDb
[Code] ....
What I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
What query would I use to build a date in m/d/yyyy format using month, day and year values from another field?
View 2 Replies View RelatedLet's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
Hi,
Could you help me with the following.
Lets say I have table with one of the column header as notes.
The notes is of type 'text'
In the notes column information like the following is typed out
MAX O.D 3.456" Min ID 1.2" and OAL 3.4"
or
MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.
So the end result should be 2 more columns with max od as one and min length as the other.
Regards
George
My frame name is "Frame1" with Option buttons "Circle" and "Square"; My code line : Name = me.Frame1.however "Name" get a value of 1 or 2 depending on the selection.how I can get a return of the String Values instead of the Numeric values.
View 3 Replies View RelatedI have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
I'm populating a combo box from a query. I'm running a DCount on 2 criteria. The user selects the criteria from 2 combo boxes. If the user types * into the combo box, I want to be able to loop through each combination from the combo box values. I need to get the list of values from the combo box and put it into a string array, however it throws a type mismatch if I did something like
BRANCHES(x)=CStr(cboBranches(x)) 'cboBranches(x)=cboBranches.Value(x)
So I tried to run the sql from vb but found out that I can't return a string value from that (vb sucks). So I'm back to trying to get the values from the combo box. Any help would be appreciated.
I have a form which displays contact details for customers. I want to add a button to the form which concatenates name and address fields, adding spaces and line breaks, and then copies the full name and address so that it can be pasted into other applications (e.g. into a letter).
To do this requires that I implement some code to perform the following actions, but I can't figure out how to do it. Can anyone help?
1. Check Title field. If it is not null, take it's value and add a space after it.
2. Check First Name field. If it is not null, take it's value add it to the output of step 1 and add a space after it.
3. Check Surname field. If it is not null, take it's value and add it to the output of step 2 and add a space and a line break after it.
4. Check the Company Name field. If it is not null, add it's value after the line break and add another line break.
5. Repeat this for every field until the end of the address is reached, then copy the result.
Thanks in advance if anyone can help.
Gary
I have got like subform which I think is the tech term I use it to filter dates on field "date raised" and open's a report depend on date's entered in txtstartdate and txtenddate it works perfectly but I need to add some check boxes to check other field's are true/false ...
Code:
Private Sub cmdPreview_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
[Code] ....
I need to add code to the text in red well I'm guessing
here goes with what I would like to add
job cancelled1 check box name field name= job cancelled
job on hold1 check box name field name = job on hold
void property1 check box name field name = void property
E.g. if I put date range in my date boxes and tick job cancelled show all record in that date range cancelled same with job on hold and void property
here some code I did but wont work as it not in the same strwhere above
Code:
if me[job cancelled1] = true then
strwhere = [job cancelled] = 1 'field name
else
strwhere [job cancelled] is null
end if
Some how need to add the strwhere to the code above ...
I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:
initial field:
xxx;yyy;ppp;yyy
targeted result:
xxx;yyy;ppp
How this could be achieved ?
My db contains an unlinked form that is used to append records to 1-3 separate tables (tests to see if master records exists, appends if needed, moves on to child records). One of the fields is a text field meant for notes. The VBA errors out whenever this text contains apostrophes or quotes.
Is there a simple way to recall the string field with quotes and apostrophes intact and append it, or will I have to validate and remove them from the string?
Hi,
I try to execute an update command i.e.:
UPDATE Results set Data = 'Heiner's key' where ValidationNumber = 1 and TestNumber = 2 and Line = 'E1'.
Access does not let me do this because the “'” in Heiner's terminates the update string prematurely.
Does anyone have a suggestion how to do this? Thank you.
I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.
Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)
The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.
So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.
I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc.
I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLSelect As String
SO_Description = "Profiles: "
Set db = CurrentDb
[Code] ....
I'm having problems with quotation marks in a sql statement. The string is an array separated by a semicolon.
120/80;70;5'6";125
this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.
I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:
original string: 120/80;70;5'6";125
strPreOpVits = "120/80;70;5''6'"';125"
mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _
"WHERE nID = " & myRecID
docmd.runsql mysql
I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error.
Here is the code that I use to convert the original string
Public Function FixQuotesInSql(strToFix As String)
Dim lgth, y As Long
Dim strTemp, char2Add As Variant
'This routine fixes the use of apostrophe and quotation marks in an SQL sequence
'If the apostrophe is at the beginning or end of the string it replaces with 3 x "'" or "'''"
'If in the middle of the string then replaces with 2 x "'" or "''"
[Code] ....
I am provided a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process.
What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).
After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.
TypeDescrPreEncEncExp
VOUJsmith-Instructor, 12/16/13$0.00$0.00$45.00
POJSmith-Instructor, 12/16/13, Course1, Parking($45.00)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Parking$0.00$45.00$0.00
VOUJsmith-Instructor, 12/16/13$0.00$0.00$221.13
POJSmith-Instructor, 12/16/13, Course1, Lodging($221.13)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Lodging$0.00$221.13$0.00
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
View 1 Replies View RelatedHi,
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are,
Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records.
Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
Thanks in advance (I hope),
Onur