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 RepliesHey all,
I have two fields 1 & 2
field 1 is a simple combo list of user defined values ie A, B, C or D
Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e
A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z
I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc
How do I do this?
Cheers all,
Matt :confused:
I am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.
View 3 Replies View RelatedWe receive hundreds of items at the time capturing the item barcode and the userID. I want to improve the process capturing the UserID once and updated the table with this entry on all empty records on the table. I have created the following script but I doesn't work.
Private Sub Command7_Click()
Dim strSQL As String
Dim strUser As String
strUser = InputBox("Scan your badge")
strSQL = "UPDATE " & tblGER_ReceivingLog & " SET " & tblGER_ReceivingLog & _
"." & UserID & "='" & strUser & "' WHERE ([" & tblGER_ReceivingLog & "]. & UserID & is null);"
DoCmd.RunSQL strSQL
End Sub
I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?
View 8 Replies View RelatedMy form contains the field Bill_Date, which may or may not be input during data entry. What I need is to run a query (I think) that allows me to input a specific date that will automatically populate all Bill_Date fields that are currently null with the date specified. Eventually this will end up on the front end where a biller will click on a link when they open the db, input the date into the field, and then process another report.
I have absolutely no idea how to even begin and was not able to locate any specific information in Help or here on the forum.
Many thanks.
I exported about 190,000 records (about half of all records in the table) from Access DB to Excel. I then updated two fields (that were previously blank on the database) on the Excel sheet with values. What is the easiest way to import these new values back into the database table so that the records receive their respective values?
View 1 Replies View RelatedI'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.
How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?
strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
[code]....
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code:
Sub Update_Dates()
Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String
[Code] ....
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
View 1 Replies View RelatedI have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
[Code] ....
I have an unbound form in which a user enters data relating to a credit dispute, and when they hit the Save button I have a sub that is intended to insert that data into a table. This is a bit out of my comfort zone, so I found some code from another forum and adjusted it to my needs. However, I'm getting errors.
The table name is tbl_complaints. The fields from the form are:
txtAcctNumber
txtClient
txtEoscarType
txtEoscarControl
cboMethodReceipt
....
My code is below.
Private Sub cmdSave_Click()
Dim StrSql As String
On Error GoTo ErrorHandler
StrSql = "INSERT INTO tbl_complaints " _
& "(ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT,
[Code] ....
The error I get is error number 3075, and it says I'm missing an operator in the section that references cboResponseType, but I can't seem to figure out what I'm missing. My head is spinning from staring at these parentheses and apostrophes for so long.
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
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
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]));
I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers
NYKU023561
TRLU102356
TCNU123023
This will search my container number field and show a pop up message box saying..
Please note below
NYKU023561 - has been found you cannot use this container
TRLU102356 - has not been found please check internal system
TCNU123023 - has beeen found you cannot use this container
Below is the basic search I had before which was all I needed.. how can I adapt this?
Code:
Private Sub Command256_Click()
If (CntSearch & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Dim CNT As String
[Code]....
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)?
Hello,
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
Thanks in advance,
--Robert
I've got the following code on a form. The entirety of the sub is listed just in case. The relevant portion is below.
Code:
Private Sub Form_Timer() 'timer set to fire once per minute
'AutoPurge old records (>7yrs) and take action if Hotword and/or NANU expire
Dim Minute As String
Minute = Right(Format(TimeValue(Now()), "hh:mm"), 1)
'Only run these every 10 minutes
[Code] .....
The part I'm having trouble with follows:
Code:
If DCount("*", "qryNotifyExpiredNANU") = 1 Then
MsgBox "The NANU " & DLookup("NANUnumber", "qryNotifyExpiredNANU") & " has expired. It has been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
ElseIf DCount("*", "qryNotifyExpiredNANU") > 1 Then
MsgBox "The NANUs " & !!!!RETRIEVED VALUE HERE!!!! & " and " & !!!!RETRIEVED VALUE HERE!!!! & " have expired. They Have been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
End If
The intent of that (incomplete) ElseIf is to provide a list of all NANUnumbers that have expired. When a single NANU expires, the DLookup works perfectly since the query only has one record. But I'm stumped on dealing with anything more than one value.
The desired result is that the value from the NANUnumber field of each resulting record in qryNotifyExpiredNANU would be used to build a string for use in the MsgBox.
Using DLookup is out (I think) because I can't identify which records the query returned to apply a WHERE clause.
I also looked into using Loop with a RecordSet, but can't figure out how to approach it.
So to sum it up, I don't know how to grab each value present in the query and I also don't know how to concatenate a string with a dynamic number of values.
I have the following fields in an A2010 form
PaymentMethod
Total
InstalmentAmounts
OurFee
InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box
Here is the current code
Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)
In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field
So
Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.
The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.
Code:
rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and scannedby <> null", cn, adOpenKeyset, adLockOptimistic
j = rs.fields(0).Value
I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
View 2 Replies View RelatedI have a script that sends a mail using outlook to a different person depending on a value in a field. I want to add values from fields to the subject and body of the mail it creates. This is what I have so far:
Function Mail()
On Error GoTo Mail_Err
If (Forms![Manage Packages]![Customer] = "MSC") Then
DoCmd.SendObject , "", "", "email", "email", "", "[ID] - [Package Name] - Delivered", "Dear," & vbNewLine
[Code] .....
In the subject and body the field ID's I want to add are marked as [ID], [Package name] etc.
I 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;