puzzling data mismatch error

I have a table where one of the fields contain values such as 26-14-2C-R1-K1 15-13-1C-R3-K6 and so on. this is a text field I am trying to find records based on the part: R1-K3, R2-K4, R5-K1 etc. I am getting weird data type mismatch error when searching with my query. For example this works: SELECT myfield as P FROM myTable WHERE myfield Is Not Null AND right(myfield,len(myfield)-9) Like 'R1- K1' ORDER BY myfield but this one using a nested OR doesnt and fails with a data mismatch error: SELECT myfield as P FROM myTable WHERE myfield Is Not Null AND (right(myfield,len(m

Spell Check specific fields on a subform

Hi, I have a form with a continuous subform that has four fields. I would like to add a button to my main form to spell check 2 of the 4 fields on every record in my continuous subform. The two fields are text input field and the other two have prepopulated information in them that does not need to be spell checked. How can I accomplish this? Thanks

Exporting curretn recordset to Excel - only working first time!

The code below works the first time it is run - the displayed records are output to a excel spreadsheet called book1 with the worksheet named as it should be. So you open a form and click the button on the ribbon referencing this code and all the displayed records are transferred to a spreadsheet. However if you then try to run the code again without first closing and reopening the form you get a blank sheet. Any ideas? Also any idea how to make this work with an open table as well as a form. If I run the form on an opne table I get the error "2475 You entered an expressi

Text box with help instruction

Access 2003. Hello Forum I have a contnuos Form with two textboxes (txtbox1, txtbox2). When I click txtbox1 I would like it to display an instruction next to the txtbox telling for example: "Enter or write the City Name". If possible not a MsgBox. How could I get that? Or can anyone point me in the direction of some documentation that might help me. Many thanks

Open form to ID and add in the record if not there

Hello, I have an issue opening a form to an ID of the record I am currently on. If I am on frmProjects and am adding a typing in a new EndUser from the dropdown in cboEndUser I want to be able to do two things: 1) Add this new EndUser name in the frmEndUserDetails in control source EndUserID (named cboEndUser) once I tab out of it 2) Have this new EndUser nae appear in the frmProjects once I finish adding details about this new End User in frmEndUser. So what happpens is that if I need to enter a new EndUser name in cboEndUser, once i tab out of cboEndUser the frmEndUserDetai

NumLock

When I use the SendKeys "{ESC}" (a very intersting instruction) in the Before Update, the NumLock will toggle Off the very first time. I have to toggle the NumLock manually back to on (one time). The second, third,... time everything is fine and the NumLock stays toggled On. Can somebody help me.

Data Entry = No

Hello, Below is vba on the On Click of a command button that is used to open a form called "frm_AltaDeRegistrosPagina1-2Analista", match the Record ID to the local form's Record ID, then make the "Data Entry" attribute = No. For some reason it is not working correctly. I need the vba to change the "Data Entry" attribute = No, so that I can modify the data on that form. The said form above is usually used to add records using the "Data Entry" attribute and via using the switchboard "Add Record" setting. Now I need to access the same form to modify records. Can you help me fix th

Double Metal Jacketed Gasket

Sinston Sealing Material Factory is one of main exporter & manufacturers of sealing= material & insulation material in China . lies in Cixi city belonging to Ningbo area o= f eastern coast in China. There are 10 series main products : Seals gasket, gland packing, gasket sheet, seal t= ools, rubber products, PTFE products, asbestos products, fiberglass products, ce= ramic fiber products & mechanical seals , High Standard and high quality are appoint= ed as fixed-point products by many domestic middle and big enterprises , are sold far= to Japan , South Korea , Brazil , USA , United

combo box troubles

Hi, I have a form where if a (vendor) combo box field is "null", a msgbox pops up asking the user to choose a vendor. After a vendor is chosen, an order form pops up with the vendor info populated. All of this is coded on a button: if isnull(forms!frmInventory.vendor) then msgbox "Please choose a Vendor", forms!frmInventory.vendor.SetFocus *****more code here***** end if forms!frmInventory.vendor.value = vendor docmd.openform "frmOrders"... vendor = forms!frmInventory.vendor.value forms!frmOrders.vendor.SetFocus forms!frmOrders.vendor.text = vendor what code do I

2 list box iteration

I tried to process something for 2 list box and need to iterate 2 of them and one iterates selected items one iterates all list For i = 1 To lst1.ListCount For Each VarItem In lst2.ItemsSelected ' my loop is here Next VarItem Next i It seems that every VarItem increase the i increase as well. I want VarItem to be inner loop which should be finish all seleted item and go to next i. Where is wrong to use above code? Any information is great appreciated,

Tracking changes to a form's fields---still searching for an answe

I have a table and module set up to track all changes made to a record on my form. I am using the following code for the module: Option Compare Database Function LogChanges(lngID As Long, Optional strField As String = "") Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim varOld As Variant Dim varNew As Variant Dim strFormName As String Dim strControlName As String varOld = Screen.ActiveControl.OldValue varNew = Screen.ActiveControl.Value strFormName = Screen.ActiveForm.Name strControlName = Screen.ActiveControl.Name Set d

Login with pwd & Open the main form depends on pwd

We have three groups of user that will use our database. Administrator (my boss & me), PowerUser (Manager / Supervisor) & NormalUser (clerk) Now, my front-end .accde will open my designed form – (main menu1) without any control. What we are planning are : 1. Users should have input their password (user can change by themselves) before they can login into the main menu. 2. Different class of user can open their own menu only (eg. Poweruser will open Frm_MainMenu1 & NormalUser will open Frm_MainMenu2 …) that depend on what password they input Could you give me some hin

After server upgrade some command buttons do not work?

I have a split database with the BE on the shared drive and the FE on each users' C: drive. We are using Access 2007. We upgraded to a new server on Thursday and on Tuesday I was notified by a user that their command buttons to open various reports were not working. The error they get is - The OpenReport action was canceled. I thought perhaps it was related to the trust center, since I had to update my trusted locations after the new install. I updated the trust center to the new server location in her copy of Access and I replaced her C: drive FE copy with a new FE copy. Netihe

Tracking changes to a form

I have a table and module set up to track all changes made to a record on my form. I am using the following code for the module: Option Compare Database Function LogChanges(lngID As Long, Optional strField As String = "") Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim varOld As Variant Dim varNew As Variant Dim strFormName As String Dim strControlName As String varOld = Screen.ActiveControl.OldValue varNew = Screen.ActiveControl.Value strFormName = Screen.ActiveForm.Name strControlName = Screen.ActiveControl.Name Set d

Adding a command to a picture

I have inserted a picture onto my form and in the Event / double click set up a macro to close the form however when i double click on my picture nothing is happening - help!!

Testing numeric input

Is there a way to prevent input a Text-when the field requires a numeric input? Perhaps there is a function that test the input-type? Something like: Sub txtField_BeforeUpdate if "txtField is not Numeric" then MsgBox "Please give a number" SendKeys "{esc}" Cancel = True end if End Sub

Multi-select list box for multi-column query

Hi, I have a list box lstBudVols from which I want users to select 4 items. Once these are selected, I want to use the selection for each as criteria for a different column in a query eg select 2010, 2011, 2012, 2013 in the list box, the output columns would be: TSL 2010 2011 2012 2013 xx xxx xxx xxx xxx I've done this earlier already using 2 different list boxes, but it would not be a smooth, well built database if the user had to select from 4 different lists. Is there a way of modifying: For Each vItm In Me.lstBudVols.ItemsSelected strWhat=etc I need to

Add totals of record type to a form footer.

Hi. I've created a form with ~60 fields. 47 of them are combo boxes with 'yes', 'no' and 'na' options available. I'm trying to create a series of text boxes at the bottom of the form to show the counts of each selection (to be used later in determining a final score, so at the footer the user would see something like Total Questions; 74 Total Not Applicable; 24 Total Yes; 45 Total No; 5 I've so far tried playing around with the expression builder using the 'count' feature but can't quite seem to get the syntax right. Advice appreciated.

AutoNumber, messed up sequence, duplicate values.

Hello, I have a database that is used by about 20+ users entering about 1000 records per day. I am noticing that they are having trouble adding records to the DB sometimes. When they have trouble, I go into the database table and try to add a record manually at the source and sometimes it tells me that the Autonumber is creating a duplicate record. As I scrolled through the data in the table I noticed the following.... Autonumber type field called "RecordID" 1-1085 looks good then it jumps to 3741 through 3749 then it jumps to 16864 through 17074 I know some peopl

Locing records i nmulti user

Hi I have a multi-user Access 2003/2007 database. In mono user mode everything works fine, but in multi user mode there seems to be occasionally a Conflict message giving the users to either Save Record, Copy to Clipboard or Drop Changes. I open my tables something like: Public Function OpenTable(strTableName As String) As DAO.Recordset On Error GoTo FoutOpenTable Set ws = DBEngine.Workspaces(0) Set db = DBEngine.Workspaces(0).Databases(0) Set OpenTable = db.OpenRecordset(strTableName, dbOpenTable) ExitFoutOpenTable: Set db = Nothing Exit Function FoutOpenTable:

coding between subforms

In Access 2007, I have a main form housing a tab control. On one page of my tab control, I have two subforms. The first subform lists my customers getting the data from a query in which I merge the first and last names and have a customer id (two fields). This form presents data in a continuous form list. The second subform has the detailed information on the customer. My goal and intent is to be able to click either on a button (linked to the customer id), the name of the customer, or even utilize toggle buttons (in which the caption is the customer name - preferred as a

Apostrophe Problem

I use a control on a form which I have concatenated from the users First and last names. All works well if I select, Harry Smith, but when I select a name with an apostrophe in it, Kelly O'Malley, I get a run time error 3077. The coding is: Private Sub Combo80_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Name] = '" & Me![Combo80] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Any suggestions? Thanks

Employee Time card form

I have a table containing two fields - Employee_ID and Time_Stamp. I've already created a form that scans in Employee_IDs; and once any Employee_ID is scanned in, the ID and scanned time is recorded to the table. In addition to what it already does, the form needs to populate the employee's punch time records for the current week. For example: (current date and time is Wednesday 9:02 am) (Form view before update) Employee ID: ___________________ (Form view after update) Employee ID: _1001 (Enter)_______ ________________________________ Employee ID: 1001 Mon: 9:00 am

Subform Code Question

I have a mainform (The One) and a subform (The Many) that displays data correctly. The problem: (The Many) can have one item or as many as nine or ten items and I need to be able to subtract an amount the user enters on the mainform from all of the subform items. The way I have it coded now it subtracts only the top or first item amount. How do you set-up a ‘Do While Not End Of File’ section of code in Access 2007? Thanks in advance -- KSmith

Continuous form record number

I have a form whose default view is 'continuous form'. Then when I click a button I need to delete the record where the mouse is (was just) placed. How can I find out the record number? Alternatively any idea is welcome. Thanks Henry