Modules & VBA :: Using Record Number In Where Statement
Jul 8, 2014
I have a form with a sub form based on a query, in one of the fields i use a dbl click to open another form. I have use this in several areas in my DB but i having issues in one section of it not opening to the correct record. i tried a few things but i am limited to what i can use. The Record_Number is an Auto number and CLASS_Num is an entered number buy the user.
I have 2 things that i can use but i can not get the Record_Number to work. When i use the Record_Number i get an Data Type Mismatch Error.
----------------------------------------------------------------------
Private Sub Combo10_DblClick(Cancel As Integer)
DoCmd.OpenForm "Documentation_Form", acNormal, , "[Record_Number] = '" & Me.Record_Number & "' and [CLASS_Num] = '" & Me.CLASS_Num & "'"
End Sub
-----------------------------------------------------------------------
how to use the Like statement when creating a record-set of data through VBA. Before I was always able to find work-arounds but now is the time to slay this issue once-and-for-all.A person can build several sales quotes for a specific company and I am trying to find the last sales quote that was built. The function is passed a variable length string and I am trying to build a recordset of all quotes based on the variable. Here is the offending line of code:
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like " & "*" & strProposalBase & "*" & " ORDER By Quotes.QuoteID DESC" (This yields an empty recordset)
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like ""*" & strProposalBase & "*"" ORDER By Quotes.QuoteID DESC" (This yields an Error message stating there is an invalid column name)
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like " & strProposalBase & "*" & " ORDER By Quotes.QuoteID DESC" (This yields an Error message stating there is incorrect syntax near the word ORDER)
I have tried different variations above and beyond these strings and get one of the three listed errors.
I have a table called login and inside that table is three columns: username, password and admin.
I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.
The problem I am facing is applying an IF statement to every record in the detail section of the subform.
I have the following code:
Code: If Me.status = "CONFIRMED" Then Me.course_ref.Enabled = False Me.course_date.Enabled = False Me.cmbModule1.Enabled = False Me.cmbModule2.Enabled = False Me.course_start_time.Enabled = False Me.course_end_time.Enabled = False Me.course_training_cost.Enabled = False End If
This is in the on load event of the subform and works 'sort of'
Basically I have a record with the status of confirmed and records without this status, but the result of the if statement is being applied to all records. Is this because I need some sort of loop? and if so how would I loop through all records in the detail and apply this if statement to them all?
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code: Private Sub Command3_Click() Dim dbs As Database Dim sqlstr As String Set dbs = CurrentDb Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "") Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
I trying trying capture the ID no. of a record for future use. I have tried different combinations on different form events. Nothing works, what am I doing wrong? The latest I have tried is the following, on the load event,
Code: Private Sub Form_Load() Dim LoadNo As String DoCmd.GoToControl "ID" LoadNo = Me.ID txtLoadNo = LoadNo End Sub
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.
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
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'm making a database that so I can log calibration information about equipment every year.I have 2 tables:
The Equipment details table The Calibration record table
ID Number is shared between the 2 tables so a calibration record can be linked to its' details.
For Example (simplified sample data):
Equipment Table ID Item Unit Type 104 Thermometer DegC PT100
Calibration Table ID Cal Point 1 Test Equipment Unit Under Test Date 104 20 21 22 06/01/15
What I want is a button on a form that creates a new blank record in my Calibration record table with the ID number already entered based on what record I selected in a combo box linked to my equipment table. I really don't know where to start.
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
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 would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
I upgraded my system to Windows 8 and Office 2013, and it's now 64 bit instead of 32 bit as before. Also note that I'm still using Access 2010 as before.So the following problem now arise in this new config that was not there before.
When you do a calculation like '332.16 - 1', the answer is now 331,16, instead of 331.16. Note the point are now replaces with a comma.
Might sound trivial, but everywhere in my code where is have a dynamic SQL like:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331.16), but now it is: INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331,16), so it is seen as an extra value and the statement fails.
So, yes I can fix this one statement, but I have multiple occurrences of this throughout my app. How do I fix this?
And changing something in my Control Panel - Regional Settings would not be the answer, as on all the client PC's it might be different.
I'm trying to calculate the number of days between two dates using the iif statement
Fields: [LDW] "Last Day Worked" [ReturnedDate]
DESCRIPTION: If ReturnedDate is null, then calculate the datediff "d" between LDW with Now(), if not, then calculate the datediff "d" between LDW with ReturnDate.
I tried this but it didn't work at all.
Days Absent: Iif(isnull([ReturnedDate],(DateDiff("d",[LDW],Now())),(DateDiff("d",[LDW],[ReturnedDate])))
create a macro that automatically fills in the next invoice number in sequence",,I'm assuming this macro would look at the value of the previous record and add a one to itI don't want to use a AutoNumber field as I might need
I started a thread on this yesterday, but realised I was some way off achieving what I want, so am starting a new thread.
Have attached copy of my db. What I want to achieve is this: From frmLayClientDetails click on the cmd button See invoice details, then click on the cmd button Preview this invoice. I want to see just the invoice for the current record – easy enough for most, and I have seen code for this elsewhere in the forum. However, clients have different invoicing addresses, depending whether they are dealt with directly (Private) or via another consultancy, hence I have two invoice layouts (OK, I’m sure there is a way of getting around this as well). So as the db is at the moment, I need to say “If the SolicitorID is 1, then open rptInvoicePrivateClients, Else open rptInvoiceLocumWork”
Can someone help me with this please? Thanks in anticipation.
I am having some issues trying to use both and & or in a If statement.
The code below is what I am trying to do and it is not working.
Code:
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Or (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "No longer eligible") Then MsgBox "Please enter Associate Name" Me.Date_authorization_expires.SetFocus
I tried the code below and it works but as soon as a I enter the Or part it will not work.
ElseIf (Len(Nz(Me.Associate_name, "")) = 0 And Me.Status <> "Expired") Then MsgBox "Please enter Associate Name" Me.Date_authorization_expires.SetFocus
I am trying to write a statement and it returns: #Type!. I know what this means but it should not give this error.I have button that copies a certain number of fields to the clipboard. The one below is one of them. Here is the coding I am using that works:
Code: =Trim(IIf([CallingContactRel] Is Not Null,[CallingContactRel] & ": " & [CallingContact],[CallingContact]))
I would like to add the following condition to this code. I have a yes/no field called BookingNoteContactExcl. If this is ticked, it should exclude [CallingContact] from being copied, in other words, return "" in the code above.
Using Access 2010. I use the following code to search for a client based on ClientID. ClientId is an Auto Number, Long Integer, format is 00000. The code works fine except if I type in the leading zeros for a ClientId that does not have at least a 1 in the first digit. For example it will not find client 00100 but will find client 10001.Is there any way of setting the Dim statement to look for a ClientID with a leading zero?
Private Sub cmdClientIdSearch_Click() Dim rs As Object Dim strCriteria As String strCriteria = InputBox("Please enter Client ID" ) If strCriteria > "" Then
I have the following code on an After Update event:
If Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" Then Me.ProgPriceTxt.Value = "85" End If
This works fine. When I add another "And" to the statement, however, it no longer functions:If
Me.Program_Type.Value = "(1) 45 Minute Formal" And Me.Cost_Category = "Full Price" And Me.PavRentCheck = False Then Me.ProgPriceTxt.Value = "85" End If
Is it possible to put three conditions into an And statement? This thread seems to imply so (it's a different situation, but it seems close enough).
I believe it doesn't have anything to do with my text boxes or fields because this same issue has occurred in other places when I tried to have three conditions in an And statement.
why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.
Code: If Nz(Me.txtrefNo, 0) = 0 Then Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1 tmpRefNo = Me.txtrefNo 'testing variable value MsgBox (tmpRefNo) Else MsgBox ("What's going on?") End If
PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?
Code: If Nz(DCount("*", "[Tblupdate]")) = 0 Then ' The count is zero i = 1 Else i = DMax("ID", "tblupdate") End If Dim ssql As String Dim j, k As String j = "P" & i k = Environ("username") ssql = "Insert into tblUpdate(Update_ID,Date,Username) values('" & j & "',#" & Format(Date, "dd/mm/yyyy") & "#,'" & k & "')" CurrentDb.Execute ssql, dbFailOnError
