crosstab query question

I am very new to this, but I would like to ask for some help. I have struggled to get the sql code for a crosstab querie (my 1st!), but I just cannot seem to get it right. This example is simpler and shorter than my actual one, but I'll get the right idea from it. I start with a querie like this; VolID LastName FirstName Tool 123 Smith Sue Hammer 124 Jones Molly Drill 123 Smith Sue Screwdriver 126 Nadeem John Screwd

syntax for default value

Hi, I have a simple syntax question. Respectively, the following lines of code will default the first PerID where Active=-1 and the first PerID where OHSRep=-1. =DFirst("PerID","tblPers","Active=-1") =DFirst("PerID","tblPers","OHSRep=-1") However, what I want is the first PerID where Active=-1 And OHSRep=-1. What is the syntax for the WHERE portion of the expression? I've tried various forms such as the following but get #Error. =DFirst("PerID","tblPers","Active=-1" And "OHSRep=-1") Thanks in anticipation of some advice. Tesa

Blinking labels are nice, but you never want them vanish into thin air, or?

Dear friends, My form comprises three radio buttons. When I press a button, its label starts blinking nicely. When I choose next record at the SAME moment the label is not visible, the label text disappears for that new record. This is quite logical, but does not reflect what a user really wants. Is there an easy workaround? Private Sub Form_Timer() If Me!RadioButton1 = True Then Me!Label1.Visible = Not Me!Label1.Visible ElseIf Me!RadioButton2 = True Then Me!Label2.Visible = Not Me!Label2.Visible ElseIf Me!RadioButton3 = True Then Me!Label3.Visible = Not Me!Label3.Visible

IIf question

I recently read that there is a danger in using IIf in VBA because both outcomes are evaluated whether this is necessary or not. For example, the following code crashes, even though division by 0 isn't an expected outcome. dblValue = IIf(intP = 0, 0, intQ/intP) This concerns me because I have lots of IIf statements in my SQL WHERE clauses, though I haven't noticed a problem. Does SQL have the same difficulty with IIf that VBA does? Vicky

User-Defined type not defined

hello I use ACCESS 2000. I'd like to run the following code borrowed from one of the Access user sites, but compiling fails at the Dim statement. ("User-defined type not defined") Private Sub ListAvailPrinters() Dim prn As Printer For Each prn In Application.Printers Debug.Print prn.DeviceName & " on " & prn.Port Next prn End Sub I suspect a Reference Library is not installed, but I'm not sure which one I need. I currently have the following Reference Libraries: Visual Basic For Applications Microsoft Access 9.0 Object Library Microsof D

subform datasheet question

I have a subform datasheet that I populate the first 2 fields by scanning barcodes. There are more fields, but I want to advance to the next record down so that we can contiune to scan in the data. The additional fields are related, but auto fill based on what is scanned in. How can I advance to the next record. I am going to try after update on the last field. Would this be the correct event? Any suggestion on the event code? -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/

Issue creating sql authentication odbc system dsn

Can anyone spot what's wrong with the code below which attempts to create a system dsn using sql server authentication? The login id and password are good for sure so is the db name and server name. Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _ (ByVal hwndParent As Long, _ ByVal fRequest As Integer, _ ByVal lpszDriver As String, _ ByVal lpszAttributes As String) As Long public CreateDSN() dim lngResult as long ' skip stuff lngResult = SQLConfigDataSource(0, _ ODBC_ADD_SYS_DSN, _ "SQL N

Tab Form Coding

I have created bound form (Tab page) and set ‘Data Entry Property’ to Yes to enter the data. So the form will open showing blank record. As I don’t want to show all the records when one opens the form. But if someone wants to search/view/update the previously entered record, How should I code the form? Thanks,

No crrent record with empty subform

I have a form with a subform in continuous view, sometimes the subform has records to display, sometimes not. There are command buttons in the header of the subform, because I want to be able to do certain things even when there are no records. If I had them in the body, they would not be visible when there were no records. When I click any of the buttons, I get a pop-up saying "No current record". I click OK and the dialog goes away, and the code under the button runs normally. There is no code associated with the Current event, no events at all associated with the form, bo

Problem displaying a form that runs queries

Hello, In Access 2007, I’m having a little issue displaying a form that runs queries on load. Below is my code. When I double click the form, the queries begin to run but the form only appears after the queries are done. If I put the code behind a command button it works fine. I can’t use a command button because I need the queries to run unattended when the DB opens. Where am I going wrong? Private Sub Form_Open(Cancel As Integer) DoCmd.MoveSize 2880, 4320 End Sub Private Sub Form_Load() DoCmd.SetWarnings False '********************************** 'QUERIES MUST BE

I need help with opening a help file

I have a form that when I open it will display the help file. Every time I try this I get the Open, Save, or Cancel commands. This is the code I'm trying to use. Me.WebBrowser0.Navigate "e:\nci database\nci taps\working taps\taps 03-03\ taps-help.chm" I don't care if it opens in a browser or not I just want it to display the help file and its content. I know I can set the Help File and Help Context Id for the individual screens. This is the main help file I'm trying to display. Thanks for your help. -- Message posted via http://www.accessmonster.com

Help with Update Query

How do I setup my code so that the following query will fire only on the items on form match the order number? UPDATE tblItemSerShipLog INNER JOIN tblLocationDataC2 ON (tblItemSerShipLog. SerialNum = tblLocationDataC2.SerialNum) AND (tblItemSerShipLog.Item = tblLocationDataC2.Item) SET tblLocationDataC2.OrderNum = [tblItemSerShipLog]. [OrderNum], tblItemSerShipLog.CustNum = [tblItemSerShipLog].[CustNum], tblLocationDataC2.Shipped = Yes, tblLocationDataC2.DateShipped = [tblItemSerShipLog].[TransDate]; -- Matt Campbell mattc (at) saunatec [dot] com Message posted via http://www

UPDATE Table with multiple textboxes

I have three unbound textboxes on a form. Each textbox is used to input the amount of money A,B,C deposited. textbox1 is labeled A, textbox2 labeled B,.... When the user clicks a button, I need to update the table to add a row for A then add a new row for B and then C. Below is the code I have so far for the update, but I can't figure out how to repeat the update for B & C. Lastly, the table has a field named 'memo'. I would like to add the textbox caption to that field on each record. So, for the first record, the deposit field would be whatever number is in the Te

Sequential Numbering

I need to create a form that will create records based on the following details. The user will tell me the quantity of serial numers they need. I get the first part of the serial number and then it's a 3 digit sequential number to the total needed. Example: K05-272-xxx The xxx is the sequence number starting with 001 to the number needed. How would I do the code to create the sequence number. Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via http://www.accessmonster.com

Change paths to linked external data

Hello. I have a database that has 3 external links in it: 1 is to a table in another Access Database. One is to an excel spread sheet. And, the third is to a .csv file. Is there a way in VBA to change the paths to these? Basically, i want to set up a form where the user can specify the new paths in text boxes if any of these files are moved. Anybody help me out? (Access 2007)

Missing Operator

I am using Access 2002. I have a listbox with two columns that allows me to choose one or more items, then generates a report for those I've chosen. I am receiving an error on some of them. It is: Syntax error (missing operator) in query expression '([txtCourseTitle] = 'Career Edge Director's Program' And [txtVendorName] = 'Career Edge Right Management')'. I thought it was the apostrophe but I have others with apostrophes and they work fine. I have some with double quotes and those are good too so I'm kind of at a loss for what the problem is. Can anyone help me out? Th

Replicate MS Excel VLOOKUP Function in MS Access

I am converting several MS Excel spreadsheets into a single MS Access 2003 database for an MS Excel User. The MS Excel VLOOKUP query I am trying to recreate is as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(EG2,EH:EI,2,FALSE)) New MS Access Table Name: 0301_ElectricitySupply_FeatureLine-up Field names for MS Excel and MS Access are as follows: MS Excel field: MS Access Field: A2 Verify Config "A2" is VLOOKUP function listed above MS Excel field:

Detecting a Character

I'm looking for a simple way to check that the text entered into a text box contains a slash at the end. I'm assuming VBA that executes On_Exit. -- Peter Marshall Manager Information Services Ohio Coatings Company

Add New Record / Make Existing Records Read Only

I would like open a form to add a new record and let the user browse existing records without being able to modify them. How can I accomplish this? Thanks

Searching Record

Hi, I just want to know something about record searching on lost event of combo box. I have a relationship database in which my first table contain staff ID & Staff Name then in another table contains different parameters along with Staff ID & Staff Name my primary key is Staff ID what i want is this once a staff select his ID in combo box on lost event of it below is the text box which should be populated with staff name. I am using Access 2007. please revert asap. -- Thanks & Regards Majid Pervaiz Operations Analyst Al Hilal Bank, Abu Dhabi, UAE Message posted via Acc

Detail in form goes blank

Thanks for reading this. I have a sub form that I use for a search box and when I type in a dummy name to check if the search is correct, the detail section blanks out. Of course, when I type in the correct name it is ok. I'm just wondering how I can keep the screen in the detail section on. I do have: Allow Additions - Yes Data Entry - No I know if I change the Data Entry to Yes then the detail will stay on. The problem with that is when I press next, it will go to the next record. If it's on the last record then it will show blank record and I have to press the Next button twi

VBA Code Written Once For All My Forms

I have a couple procedures that I use for dozens of forms in teh same database. The code for this prodecure is identical. It's not in every form, but many. Can I reference such a procedure from any form I want, but write the procedure once?

Concatenation

Hi all, I have to tables and two form, Tbl A = Form A, Tbl B = From B. Form B is a subform to Form A. On the “Load event” of Form B i want field “Bananas” to become populated with data from fields “Fruits”,”Basket”,”Colour” in Form A Me![Bananas] = Forms![A].[Fruits]&””&[Basket] etc.. does not function.. One way to “almost” solve this would be to build Form B on a query were I Concatenate the fields..but I want form B to create a new record on load (Data Entry = Yes) and the way I see it..it cannot create a new record based on this query... Thankful for assistance!

incorrect null value in combobox

Hello I have a combo with 2 columns. Everything is set up corectly (bound column, no of columns etc). Rowsource is a query which has 2 columns as well. All goes fine but from time to time, i don't know for what reason, I get the result me.combo.colum(0) or (1) = Null even though I should have not null values (the bound query returns not null values, I have checked) It is intriguing that this does not happen every time and also if I check the value of my combo with me.combo than it returns the correct result not null. Can I get the value of my second column other than me.

Quick check with a second form

In my form Form1 i want to open a second form , Form2, but showing only the row where the focus is for example the field Productcode from the first form to show the same field Productcode from the second form.How to do it automatically when changing the focus in the first form? -- Message posted via http://www.accessmonster.com

subforms

Hello everyone, I have two subform in my mainform, frmOrder and frmPayments with record source of a select query. I have this field in my tblOrder CustomerName Date RefNo Amount Primary Key RefNo I have this field in my tblOrder CustrName DatePaid RefNoPaid Amount Primary Key RefNoPaid that is how I build my tables and query them. I am planning that when in my frmOrders will automatically set the checkbox field set to YES when the payment from frmPayments subform equals to the amount from frmOrders? is it possible? Hopefully this is clear.. Thank you in advanc

How can I allow hyphen (or dash) in this validation rule?

In the form, users may enter alphanumerical data. The only special characters I want to allow are periods (.) and hyphens (-). How can I modify this validation rule to allow a hyphen? Rule is: Is Null Or Not Like "*[!((a-z) or (0-9) or (.))]*"

VBA sort code DESCENDING

I am using VBA code behind a form, to sort as follows: Private Sub ob_org_Click() Me.OrderBy = "org" Me.OrderByOn = True End Sub where org is the fieldname in the table. It works great. No problem. I need to click either the same button or another button to sort the other way (DESC).

Parent.RecordSource in a popup

On another post (below), I read that a textbox can be created to access the record source of the form on which it resides. Can I create a popup form (lets call it SQL POPUP) (which will activate from clicking a button on the form having the button MAINFORM) in which SQL POPUP has a textbox containing the recordsource of the MAINFORM? 1/17/2010 12:25 PM PST Author: Presto ---snippet--- Title of Post: Show record source in a text box I need to weed out a very overcrowded database. I need to remove ... ---end snippet--- Marshall Barton wrote: Me is not valid in a contro

How Can You Force a Particular Popup Form Height and Width?

I am looking to force each popup form to the dimensions of height and width that I want. I tried setting the Border Style Property to Sizeable and Auto Resize to No, however when I go from Design View to Form View for the Popup Form, I have to resize the form each time; when I set the Border Style to "Thin" I cannot change the dimensions.

FormErr 2473 0* expression On Open you entered

I am getting these 2 errors messages and I have not been successful in finding any information that will tell me what is causing the problem. I have installed an application which was written for MS ACCESS run-time and VB and it is giving this error message. Can you tell me where to look as the messages does not help.

Table Locking Problems

Greetings, I use the following code to create a relationship between table A and table B: Set rel = db.CreateRelation("A_B") rel.Table = "A" rel.ForeignTable = "B" Set fld = rel.CreateField("AID") fld.ForeignName = "BID" rel.Fields.Append fld db.Relations.Append rel This code works as expected, but a few lines later I call DLookup() on table 'B' and I get the following error: "Run-time error '3008': The table 'B' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically." If I comment

Display Table Name

Hi, Is there any way to code a text box to display the name of the table that I'm using for my query? I don't want to hard code it, since I have other code that changes the RecordSet property. Is there a way to have it dynamically become the name of the table that one of the fields is coming from? Thanks, John Schping

SQL Statement

Hi Guys, I have this sql statement, it works but I want to display a message if the record is not found and continue if record is found, my question is how can I capture these events, I will really appreciate any help Thanks Set rs = db.OpenRecordset( _ "select JobName, DocketGenInfo " & _ "from tblMDocket " & _ "where DocketNumber = " & Me.ComboDocketRef)

asigning a help topic to a cmd button

Good morning, I have a help menu [taps-help.chm] and it appears to work when I press {F1}. I set up the control Help File and left the Help context Id blank {0}. I went to the button {cmdHelp} and put the Help Context Id to number {30} which is a page number. When I pressed the button, nothing happens. When I pressed the {F1} key then it came up. What I'd like it to do is to (I guess) is when I press the button to have it emulate the {F1} button. Can this be done or is there another way? Thanks for your help and reply. -- Message posted via http://www.accessmonster.com

DAO and Table Locking

Greetings, I've written some code to create a relationship between table A and table B. The code looks something like this: Set rel = db.CreateRelation("A_B") rel.Table = "A" rel.ForeignTable = "B" Set fld = rel.CreateField("AID") fld.ForeignName = "BID" rel.Fields.Append fld db.Relations.Append rel This code works just fine except that a few lines later, I use DLookup() to find a row in table B, but I then get an error that table B is exclusively locked and cannot be accessed. Can anyone clarify why table B has been locked and how to unlock it? Thanks. Jon

Opening a new record form but allowing searches

I changed the "Data Entry to Yes" to have my forms open for new records, but it seems to have disabled my ability to move from record to record or allow for searches. Is there another way that I could have set it up so that it allows for both?

Multi fields search form

I have a form with multiple combo boxes. I need to find all records that maches the seleted criteria (Combo boxes). The criteria runs from multiple tables. My problem is that I cannot get it to work, so It will return only exact matches since the user will not always select all fields. Sometimes they will select only two fields, and it causes a problem with nulls values from the form. SELECT Tbl_Employee.Employee_Name, Tbl_Employee.Department, TblEmp_Domain_Knowledge.[Domain Knowledge], TblEmp_System_Knowledge.Emp_System_Knowledge, TblTaskForce.TForce_Name FROM ((Tbl_Employe

change query criteria dynamically with value from a 2nd form

Hello, I have a query with multiple criteria passed from a form named "main". I want to pass the criteria to the same query from a different form from the click event of a hyperlink field returned from a different query. How do i pass / change the current query criteria from its current value to the value coming from the second form dynamically. My code in the query design looks like From Form main (To the same field in the query design) Nz([Forms]![Main]![txt_fin_nbr],"00000") When the criteria is not coming from form Main, i want it to be Nz([Forms]![frm_display_mul

SQL statement not working

I have a login form that will login who is using the database and the date. It has the following code connected to the onClick event of an OK button: Dim rosterSQL As String Set rosterSQL = "Insert into tblLOGIN([rosterID], [loginDate]) Values ('" & Me.rosterID & "', Now() );" DoCmd.SetWarnings (0) DoCmd.RunSQL rosterSQL please note the tick marks vs. quotes around rosterID: ' " & Me.RosterID & " ' The form has rosterID as a field. But when I run the code I get a "Compile error Object required" and the following is highlighted in the code: . RosterID Any idea what my

Hyperlinks to pdfs and word documents are not working

My Access database was recently upgraded to Access 2007. Now all of the hyperlinks I have to pdfs and word documents are not working. In browsing "external data" I see that I can link to text files and excel files, etc. Please explain why my links to pdfs and word documents no longer are working and how I can fix them. -- Archivist

Date Picker issue

Greets to the intelligent among us...... Hi All, I have an anomaly happening with the (date picker??) I load a form to edit a record in History table using among other code, the lines Me.txtFromDate = Me.cboHistEditLookup.Column(4) Me.txtToDate = Me.cboHistEditLookup.Column(5) Then "On Click" to run my update SQL I first do the following check If Me.txtFromDate.Value > Me.txtToDate.Value Then MsgBox " The Ending Date Cannot be Before the Starting Date " Me.txtToDate.SetFocus Exit Sub End If However If when the form opens I click on the txtFromdate & use the date pi

Wrong data is presented after query

The data base consists of 2 key reference tables Scanner Models and Scanner Parts. As this is a many-to-many relationaship and there is a linking table ModelPart. A third reference table holds Scanner Makes which has a many-to-one relationship with the Scanner Models tables. When work is carried out on customers Scanner a Service Report is input. The Service Report consists of a main form (recording dates, work done etc) and a subform (to enter details of parts used). The JobNo (Service Report number) is the Master Link Field and Child Link Field On entereing a service r

Right justified field displays left justified

Hi, I'm on Access via Office XP Pro. I have a continuous form. That form has a text box control that I load as the result of a choice in a combo box. The combo box has a drop down list of class number and dates. When the user chooses a class, I extract the number of availabe slot (and format the data) by : intNoOpen = Nz(Me.cboClassNo.Column(6), 0) If intNoOpen = 0 Then Me.txtOpenClosed.ForeColor = 185 ' Class closed - Bright Red Me.txtOpenClosed.FontBold = True Else Me.txtOpenClosed.ForeColor = 4227072 ' Class op

Ensure correct date sequence

I am keying in data into a results table and I need to setup a checking process during the data entry so that each new results record is dated is after, not before or equal to, the date in the previous records. Does anyone know how to set this up? In other words, I need Access to prevent both an earlier date or the same date already entered from any previous records. Many thanks.

Exit button

I have a form in an Access 2007 database, that has an Exit button with a hyperlink to an online file. What I'm trying to do is when you click the Exit button, the database closes and opens the online file. Everything works the way I want except for one small piece - When the database closes, I get an error message saying that Access was in the middle of doing something when I closed and asks if I want to restart Access. Even though the database closes and I get this error, the online file still opens the way it should. My question is - How do I avoid having the error pop up

oh...my...God...I'm having my day with this crap...

I have a textbox where user enters a PIN the sub pin value is tested in the BeforeUpdate event: Private Sub pin_BeforeUpdate(Cancel As Integer) If Not (pin.Text Like "*[A-Z]*[A-Z]*[A-Z]*" And pin.Text Like "*#*#*#*") And Not pin.Text = vbNullString Then msgtxt = "Il PIN digitato non rispetta il formato previsto." & vbCrLf & _ "Inserimento annullato." Call ShowMsg(red, msgtxt) msgtxt = vbNullString Cancel = -1 End If End Sub This will let me move on other controls if the value for the PIN is in the right format or if I DELET

Conditionally hide columns in Access 2003 Code

I have code which unhides the columns on a subform based on a selection made in the main form. It works fine, but now I need to do the same thing but include two columns. I need to hide both TargetAudience and ProductMarketed. Can I use "and" or do I have to reference the other control independently. I am not including all of the code but just what I need to change I think. Dim frm As Form Dim ctl As Control Dim ctlLbl As Control Dim ctlHld As Control Set ctl = Forms!frmPaymentEntryforDC![sbfEventsDC subform].Form![sbfDCEventDatails subform].Form!ProductMarketed

DLookup Issue

Hey all, I'm working on an access database and on the MainPage form I'm looking to populate data using DLookup. I'm trying to populate a text box with data from the Manager column of the EMP table where the Full Name field in the table matches the EmpName selection on the current form. So far I'm only getting either a blank field or the first field in the table for everyone. The code I'm using is: =DLookUp("[Manager]","[EMP]","[Full Name] = [EmpName] ") Any help or suggestions to get this to work would be greatly appreciated. Thanks!

subform in subform

Hi, I've got a form F and subform S1 in it. And inside S1 I've got subform S2. How do I reference a text box in S2 from VB code? thanx alek_mil