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.
...seems such a simple thing to do but I'm completly stuck. Everything I've found on the forum suggests alternatives to doing to above i.e. setting the query that produces the list to produce that report print directly but that wont work for this situation unfortunately.:eek:
Some background.... I have a form (lucky me) in which the user enters 3 category levels and a branch id, they click a button to generate a list of 50 random stock ids so they can be checked at the branch. A preview of the Stockid's (along with description and Stocklevels) is shown in a list on the form. The stock manager then needs to print the list box contents. If the query is re-run it will differ from what is shown in the list so I need to avoid this.
on my form i have two unbound combo boxes and an unbound listbox. when i delete or go to a new record these still contain the data from the previous record. what is the code to clear them? have looked through the propeties and there is no useful 'clear' event that i can see. :confused: cheers!
Hi Everyone again, (i'll try my best to explain) (Sorry if it doesn't make sense)
I have a main form - everything on the formn is linked to the User ID
I have created a List box1 which contains many selection. (Lets say a menu for food) Once one item has been selected i have a button which adds the selected choice to another List Box2 this can be repeated to show Every item the client has selected.
I have linked both List Boxes to the User ID
This all works fine, it reconises the user IDs on every record but i would like the List Box2 to be clear instead of holding all the previous records data when a new record is created.
I have 12 folders (User1 - User12) located in "c:users..." When I select a UserName from a combo box in a form e.g. User3 , a list of all files with extensions ".doc" in the respective directory (c:usersuser3*.doc) should render with a hyperlink to the file / s.
I have tried variouse "Select Case" and "FileSearch, FoundFiles" scripts, to no avail.
Hi, I'm making a form where the contents of a field is determined on the contents of another field in another form. I thought an IIF function would work, but when I tried it the contents says #NAME.
I go to open my Access 2000 database this morning and the contents: tables, queries, forms, reports, macros...all gone! The database file is still there, and when I go to view it's properties, I verified that it is the database that I created back in April and not somebody that overwrote my database using the same filename.
Is there any technical explanation as to how this could have happened? Or is it undoubtedly the result of human intervention?
I placed it on a network drive here at work.
I kept a copy thank god, but the copy is a couple weeks old and I'll lose alot of recent data. I've put in a request to have them restore the file from when it was last used Friday.
Just wondering what could have happened so I can prevent this from happening again.
sorry for the title. i didnt know what to write. this is the situation:
i have a form where it populates a table in access (ssmdata) from an oracle table w_ssm_data). this works perfectly.
Then the form read the hole new table in access (ssmdata) to update a specific field.
Problem: If i open the access table (ssmdata) it is sorted by the flight fields. However, when the form reads the table in order to update the specific field the table seems to not to be sorted by the flight field.
More info: The ssmdata table in access has many primary keys (flight, departure,arrival,caterer,frequency and aircraft).
If i take a look to the W_ssm_data table in oracle it is not sorted by flight and it is sorted in the same way the form is reading the ssmdata table in access.
example: table in access: flight dep arr caterer aircraft frequency 1 a b a 111 67 1 a b a 222 134 2 c s c 111 5
table in oracle: flight dep arr caterer aircraft frequency 2 c s c 111 5 1 a b a 111 67 65 t h t 252 1
when the update step starts it reads the access table as it looks in oracle. but if i open the table in access it looks sorted by flight.
I have a new job at a company that had someone else develop their Access application. My boss has asked me to print out all of the options in several comboboxes so he can have me either add to or delete some of the items in the list.
Is there a way I can copy all of the items in the list of a combobox? :confused: These comboboxes are not bound to any tables.
I hope I have explained this well enough for everyone to understand what I am asking.
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:
'= 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.
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?
I'm using access 2003, and for some stupid reason they decided to remove the "clear" method from listboxes... In previous access version I could clear a listbox using "listbox.clear", but now I have no idea. Does anyone know a good way to clear listboxes in Access 2003.
I have some code that looks like this to control a form;
Private Sub btnAdd_Click() Dim UserName As String Dim Initials As String Dim Password As String Dim OutlookName As String Dim rst As DAO.Recordset
'Check each control, is their a value? if not, set focus to control If IsNull(txtUserName) Then MsgBox "You did not enter a new UserName nobby!" Me!txtUserName.SetFocus Exit Sub
ElseIf IsNull(txtInitials) Then ' return value of UserName variable; MsgBox "You have not entered any initials for user: '" & Me!txtUserName & "'" Me!txtInitials.SetFocus Exit Sub
ElseIf IsNull(txtPassword) Then MsgBox "You must create a password for user: '" & Me!txtUserName & "'" Me!txtPassword.SetFocus Exit Sub
ElseIf IsNull(txtOutlookName) Then MsgBox "You must enter a Outlook name for: '" & Me!txtUserName & "'" Me!txtOutlookName.SetFocus Exit Sub
End If ' Pass the variables to the table. Set rst = CurrentDb.OpenRecordset(("Users"), dbOpenDynaset, [dbSeeChanges]) With rst .AddNew ![User] = Me!txtUserName ![Password] = Me!txtPassword ![Initials] = Me!txtInitials ![OutlookName] = Me!txtOutlookName ![Level] = 1 ![Select] = 0 ![dummy] = Null .Update .Close Set rst = Nothing End With DoCmd.Close End Sub
Private Sub btnCancel_Click() ' Confirm Cancellation Box If MsgBox("Are you sure you want to quit?", vbYesNo, "Caution") = vbYes Then DoCmd.Close Else DoCmd.CancelEvent End If
End Sub
What I really want to do is once the update has occured is set a label I have as hidden, to show and to clear all the controls.
Thanks, Now I can populate text fields by selecting any one of the combo box selection.
But I need to clear the form to insert next record. Currently I am inserting record by using Save_cmd button. I can insert second record but couldnot clear all the fields after inserting automatically. So I am doing clear all the fields manually or overwriting values.
I have a part of the form with the following fields
Title Name Address1 Address2 Area ...
Address2(street) field is a drop downbox, once selected the area text field will be automatically shown what I got in AfterUpdate event on Street is as below Me.txtArea = [cboAddress2].[Column](2)
the problem with this is, even when I move to another record the Area field doesn't clear up which is still using the old Area info that has been selected before,
I belive I have to have some Clear Up function on somewhere, not sure exactly where and what Code will clear up the value,
I have a SQL Database that is housing my tables I have an Access Front end that is Allowing users to view and edit the data residing on the SQL Database.
I have write/modify rights on the database and as such I wrote the code , OnLoad of Form, to Automatically set the Form to "Add Record", thus clearing all the TextBoxes and ComboBoxes. That works alright.
My problem is when a user without write/modify rights opens the Form I am getting an Error "Cannot go to specified Record". If I understand this right this is happening because they do not have suffeciant rights.
How Can I get a form to load and display all the textboxes and ComboBoxes BLANK....this only way I can think of is to set the form to Add Record.
How do I clear a filter ? When I re-open a form that is based on a stored parameter query, I get a message about "apply/filter that cannot be applied..."
So, as a result, I put in some code for that command button that closes the form, then reopens it, and I get my prompt from the query. Isnt there a way to clear the filter without closing the form? It looks funny to me and I know the users will have a cow. I sure appreciate any ideas.Im digging around to see what I can do quickly, but I cant find anything that works. thanks a lot!
Private sub Form_DatasetChange() Text28.Text.Clear End Sub
I am wanting to move between records but when I return to the records I have just moved from I want to clear some of the text boxes. This code is not working though. Any suggestions
