I have got a form with a couple of dlookups on it, when they return an error it pops the debug message up and if there is a value in the field already it just leaves that value there.
How can I suppress the message and clear the field and move on to the next line of code?
Greetings all,
I'm an Access newb, so please be gentle!
Using Access XP, I have a number of unbound combo boxes I use as search tools to locate specific records. Here is the code for one of them:
Private Sub Combo40_AfterUpdate()
Me.JobNumber.SetFocus
If Not IsNull(Me.Combo40) Then
DoCmd.FindRecord Me.Combo40
End If
End Sub
I think I need to add something along the lines of:
Me.Combo40.SetFocus
Me.Combo40.Text = ""
To get it to clear the combo box after the FindRecord has executed. However, when I drop those 2 lines in either before or after the "End If", I get this:
"RunTime Error: 2115. The Macro or Function set to the BeforeUpdate Validation rule property for this field is preventing MS Access from saving the data in the field."
So, I hope this doesn't mean that you can't clear the combo box choice because it interferes with the FindRecord part of the code...Anyone know how to get the result I'm looking for? To define it clearly: I want the combo box selection area to clear the choice after the find record has succesfully completed. I don't want to clear the choices, just not show anything after a search completes.
Thanks in advance. I'm sure this one isn't that hard, but it is eluding me and I've only been doing access for about 3 months.
-Nick
I have a search form that's slightly modified search form modeled after the one available here on this site. It's a main form with a list box. I use the listbox to narrow down the results I want from the search function.
The search details are shown in a sub form that contains sub-sub forms
As seen in the code below, I am using the listbox to set a filter on the subform . Works famously until I search for something that does not exist in the database. I then receive runtime error 3075.
I believe it's related to the way I'm showing the details in the subform (by enabling a filter). If there is no record in the database that match the search criteria, I don't want any error messages or pop-ups and preferably would like the listbox to be blank.
Attached a screenshot of my form.
red = main form
green = subform
blue = sub-subforms
tan = obscuring the search results
Code:
Private Sub SearchFor_Change()
Dim vSearchString As String
vSearchString = SearchFor.Tex
SrchText.Value = vSearchString
Me.SearchResults.Requery
[Code] .....
I have a form with a subform. On the subform I have a field that has customer number in it, on the main form there is a field that will need to say "none" if the field with the customer number is empty, and empty if the customer number field is filled in. the field will not print out in the letter if there is no data on it.
the customer number comes from the table "CustomerNumbersData"
the the field that needs to be empty on the main folder comes from table "CustomerData"
using Ms access 2010.I have two text boxes where depending on what is entered on the first the 2nd disabled.that works fine but if I make a mistake and enters a value on the 2nd while it is enabled and return back to the first and enters a value which disable the 2nd, it disable it ok but keeps the value and even save it to the table.
Private Sub q300_AfterUpdate()
If Me.q300 = "1" Then
Me.q300_1.Text = ""
Me.q300_1.Enabled = False
Me.q301.SetFocus
Else
Me.q300_1.Enabled = True
End If
End Sub
have this code:
Private Sub
Job_Number_BeforeUpdate(Cancel As Integer)
If
IsNull(DLookup("[Job Number]", "Job", "[Job Number]= " & Me.[Job Number]
& " ")) Then
MsgBox "Job Number doesn't exist Enter a job
number that already exist."
Me.[Job Number].Undo
Cancel = True
End If
End Sub
It is not clearing the Job Number Field, and also it is not letting me to close the form without entering the Job number that already exist. If I try to close the form without entering the job number it gives me run time error " syntax error (missing operator) query expression '[Job Number] = '."
I'm currently making an Experience Statement for my company. In one of the forms I've created I an Industry sort that is weighted and connected to a table so that it'll show a report based on the numbers provided IE. 1-5 One being shown first and a blank statement not showing on the report at all.
I have the report function working correctly the only thing I would like is a cmd button that clears sort table of numbers so that it doesn't show anything and can start fresh. What VBA code shall I use?
I was thinking something along the lines of this, but it's not working.
Dim s as String
s = "UPDATE All_Projects_Sort_Table SET Industry Sort = Null;"
CurrentDb.Execute s
Requery
Attached are some images ...
I have had to copy a column of dates to a new column to extract the month and be able to make subtotals based on each month.Now I am trying to tidy up the column by removing all the cells containing the month and leave behind the subtotals.
I have got a little way there using:
lastrow2 = Range("A1").End(xlDown).Row
With Range("A1:A" & lastrow2)
Set RngFnd = .Find(Total).
Set rngDelete = .ColumnDifferences(Comparison:=RngFnd)
rngDelete.ClearContents
But it only clears the cells down to the first subtotal so how can I get this carry on?
Alternatively I could use code to find the subtotal, and then cut and paste it into the next column over if that would be easier?
I have an append query that adds data to a table in another database, but I want it to replace the data every time it's run. How do I clear the data before running the append query? The databases are not linked, and I don't want them linked.
View 9 Replies View RelatedI've put the following Function together to clear out unwanted bits before exporting as csv. I was just wondering if there's a way of holding the table names together with a total count of each item replaced? I wasn't sure if these were stored and could be returned?
Code:
Public Function ClearCommas()
On Error GoTo ClearErr
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fld As DAO.Field
[Code] .....
I am calling a stored proc and passing some params (sql). i need to loop through the execute a few times though before I kill the connection.
I am getting an error saying there are too many params on the second run through. What i need is to clear the param list once it's executed once.
Is this possible or to i need to keep killing the conn then re-connecting to the db?
I am trying to clear a textbox after the user enters an invalid date and I do not know why the following code is not working:
Private Sub txtStart_AfterUpdate()
If Not IsNull(Me.txtStart) Then
If Not IsDate(Me.txtStart) Then
MsgBox "You have not entered a valid date"
Me.txtStart = Null
Me.txtStart.SetFocus
End If
End If
End Sub
I have a form with field [Status]. When [Status] is set to "Will Attend" I can input the country of birth in field [Country_Birth]. My question is there something where if [Status] field is set back to null field [Country_Birth] will automatically clear?
View 2 Replies View RelatedHow can I loop loop through a tabular form to clear each value in a field of all rows ? I tried the below code, but it did not work.
Loopcnt = DCount("*", "Budget")
For Loopcnt = 1 To Loopcnt
[Forms]![Budget Form].[Newbudgt] = Null
DoCmd.GoToRecord , , acNext
Next Loopcnt
I would like to open an Excel workbook from MS Access and clear cell contents, or just delete some records in a specific worksheet.
If you open the test workbook, cell contents in RAW need to be deleted by calling from Access.
I have produced some code but it's partially working.
Code:
Sub TestFileOpened()
' Test to see if the file is open.
If IsFileOpen("test.xls") Then
' Display a message stating the file in use.
MsgBox "File already in use!"
[Code] ....
If you put this in a standard module in access, the function works, but the part that doesn't work is where it says "activesheet". It somehow tries to recognize it as a variable, but it's not going to be a variable.
The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp.
The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query.
The second piece of code stores this information in a table called temp
I would like to be able to do 2 things with this;
1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs)
2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible.
The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp.
Any ideas greatly appreciated.
Here is the existing code …
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim db As Database
Dim rsRjobs As Recordset
Dim rsRapps As Recordset
Dim LengthofUnionSQL As Long
Dim sql As String
Dim UnionSQL As String
Set db = CurrentDb
Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
Do While Not rsRjobs.EOF
UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union "
rsRjobs.MoveNext
Loop
'following two lines are to remove the trailing word Union from the string unionsql
LengthofUnionSQL = Len(UnionSQL)
UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7)
' Now variable Unionsql will hold the value something like
' Select ObjectID, SearchNo, DateSearched, Consultant from J000145
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147
MsgBox UnionSQL
Set db = CurrentDb
Dim rsUnionquery As Recordset
Dim rstemp As Recordset
Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges)
Set rsUnionquery = db.OpenRecordset(UnionSQL)
Do While Not rsUnionquery.EOF
rstemp.AddNew
rstemp!ObjectID = rsUnionquery!ObjectID
rstemp!SearchNo = rsUnionquery!SearchNo
rstemp!DateSearched = rsUnionquery!DateSearched
rstemp!Consultant = rsUnionquery!Consultant
rstemp!jobID = rsUnionquery!jobID
rstemp.Update
rsUnionquery.MoveNext
Loop
End Sub
I am working on form where the user selects either "IN" or "OUT" from a dropdown of field name "CheckOut" in Frm1.
If they select "OUT" they will in turn need to fill in 2 additional fields. When they change the value back from "OUT" to "IN",
I want those other fields to be cleared of data for just this record so next time they change back to "OUT" from "IN" those 2 additional fields are already blank.
I am importing .txt files into Access table via VBA code (i.e., not via Saved Import Spec). Is there a way to trap the error if a particular field does not get imported due to incorrect format? When you import via Saved Import Spec and there are errors in formatting, Access generates an 'ImportErrors' table, which tells you which fields could not be updated.
Is there a way to generate a similar 'ImportErrors' table with VBA error checking?
I am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
I'm new to programming with Access but am attempting to create a new field in a table with an new date based on existing fields in the table.
The current fields are [Frequency], integer, [Risk], text, [Last Audit Date], date/time, and the new field is [Next Audit Date]. [Frequency] is a calculated field based only on [Risk] and is equal to "5" if [Risk] is "Low" and is "3" is [Risk] is "Medium" or "High", and [Frequency] is blank if [Risk] is (thus far it has never been empty).
What I need the new calculated field to do is return "N/A" (or blank, or anything easily separated really) if [Frequency] is blank, or if [Risk] is "Low" or "Medium". If [Risk] is "High", [Next Audit Date] should be equal to [Last Audit Date] plus 3 years. When I try to save the code, I get this message: "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server."
This is my code now:
IIf(IsNull([Frequency]),"",IIf([Risk]="Low","N/A",IIf([Risk]="Medium","N/A",IIf([Last Audit Date]="N/A","N/A",[Last Audit Date]+Year(3)))))
I'm getting error 2391 field 'f1' doesn't exist in destination table
Code:
Dim FileBrowse As Office.FileDialog
Dim varFile As Variant
Dim sFile As String
Set FileBrowse = Application.FileDialog(msoFileDialogFilePicker)
[Code] ....
Code:
Private Sub Consolidate_Click()
Dim temp As Variant
Excel.Application.Visible = True
temp = Dir(CurrentProject.Path & "Inputs")
Do While temp <> vbNullString
[Code] ....
From the second iteration its not picking the error.
I use access 2010.
I am assuming by the error code, one can not use a calculated field to calculate another field.
what's the best way of clearing the contents of a combo box? i don't want to change the row source, just make it blank as though a selection has not been made.
i have two unrelated combo boxes on a form; there will probably be more later. when i pick a value from one i want the other(s) to clear.
on the after update event of each combo i've tried clearing the contents of the other combo with these:
- me.othercombo = null
- me.othercombo = 0
- me.othercombo = ""
- me.othercombo.value = ...
- me.othercombo.undo
- etc.
'= null' clears the other combo.
'= 0' also clears the other combo.
but i think i'm missing something here. each combo also has a cmdbutton that will take the user to another form based on the selection made.
if i use '= 0', the other combo clears but its cmdbutton still works. even if the combo appears to be empty its associated cmdbutton still opens the next form (it should produce a msgbox asking the user to make a selection).
'= null' clears the combo and its cmdbutton produces the appropriate message to make a selection. so that's what i'm using right now. is this the best way? can something = null?
I need to b able to clear records from 3 tables with one click
I tried opening each as a record set and looping through deleting each in turn with a loop, however this I know to be poor practice and it also errors as i cannot delete a record that is part of a relationship.
is there a more suitable way to do this?
Hi:
I have a table setup to enter dates for each person that requires safety quizes for the month those that don't have an "N/A" for the month by thier names.
Each year I have to start over and it is a real pain to manualy delete all the dates for each person for Jan through December.
I only want the dates gone not the name or the N/A values.
Is there a sub or query that will search each record and only delete the dates?
Thanks
Charles