Hello, I generally just use search and can find help to what I am looking for ....

However, this one eludes me.

On a FORM, I have 2 Objects. A check box and a combo box.


I have checkTax set to: YES/NO

I have cboAccount functions with a rowsource lookup: SELECT Accounts.* FROM Accounts;

Here is the layout...

6100-0000 = NO
6200-0000 = NO
5430-0100 = YES
5475-0100 = YES

Now if I select 6100-0000 or 6200-0000 in my drop down list in the combo box I want the checkbox to remain unchecked. Yet, if I select 5430-0100 or 5475-0100 in my drop down list in the combo box I want the checkbox to be checked.

I'm pretty sure this is doable, I am just having a difficult time in finding out how by looking on my own. If someone can, please help.



Post your answer or comment

comments powered by Disqus

I have done a search and can't find an answer to this.

I have a main form and when you start to fill in the fields it will fill the default values for the others. If I start filling in the subform before entering in the main form the data is lost. Is there a way in which when entering data in the subform it will pick the default values in the main form?


Hello All,
I've done a search and can't seem to find the answer to this seemingly easy issue (easy except for the fact that I cannot get it work):

I have a form with multiple subforms displaying data from the same table(i hide/unhide the subforms on button selections). The main table has no recordsource, only the subforms - so there is no linking fields. When a user selects a record in one subform, I need the same record selected in all other subforms as well. There muist be a simple way in code to achieve this, but I can't seem to make it work.


I've run several searches and can't seem to find what I'm looking for, so here goes:

Have a table with several (maybe as many as 30) dates related to each employee, such as:

NAME-------CLASS 1-----CLASS 2----CLASS 3
J.Johnson.....10/16/05 ......12/17/05...12/25/05

Not all employees will have entries in every class.

Need a query to result in most recent Date for each employee, such as:
J.Johnson......CLASS 3........12/25/05
S.Smith.........CLASS 2.......10/15/05

Any suggestions?


I have done a search and can't seem to find anything on synchronized combo boxes,


If i had tblCateories with CategoryID & CategoryName, And tblProducts with ProductID, ProductName how would i sychronize these. I have seen the example on Microsoft but cant seem to follow it.



I was doing some searching on the web as well as here looking for a way to turn the split form into a search form. Essentially i would like to have the splitform filter the results on the bottom. I do not want to bid it to a query or something as I want to retain the ability to edit the table directly after filtering.

IS there a way to do this without havign a huge vba procedure? I have about 8 fields that I would like to utilize in various combination at the users discretion. I saw something simalar but no exactly what I needed out there on the web but it was very messy.

Thanks in advnace.

Hello All,
I have searched and can't seem to figure this one out.

I have a form with subforms, etc.
There is a field on one of the subforms which happens to be an autonumber field.

I am trying to run a query that uses that field as the criteria. For right now, I'm just running the query "outside" the form, but while the form is open.

My query references the field like this:
DBOrderID: [Forms]![frmSelCustomers]![frmOrders].[form]![OrdID]
Typically I have to do trial and error to make sure my code is right and I changed the OrdID to another field on the form and it works fine.

Anyway, when I run the query, I get strange characters in the place of OrdID. Here are examples of the characters I get and the OrdID that is on the form.
78581 = ㋵
91714 = 時
97088 = 筀
FYI - the OrdID is already on the form (not like its trying to create a new autonumber at the same time).

I tried changing the Format on query field to general number but that didn't do anything.

SO, my question .... should I be able to reference an autonumber field?
If so, any suggestions what I'm doing wrong?

Thanks in advance!!

I've just started with Access and SQL databases.
I've now created an input window with mostly y/n check boxes and drop down lists.
Now i like to save my data in my main dB after I've ented the data. However I use another dB to get names using the drop down lists. How can i save the those name (using the drop down list) together with all other info in my main dB?

My second question is that i'm using Access 2002 but i dont seem to have the toggle button option in my tools palette nor the tab option. Is that normal? When i search using the help both of them DO come up in the help..

Hello. I am a new here and have found this resource to be vastly informative. Much thanks to everyone. With that said, I am struggling with the following issue:

Currently, the only way I know how to retrieve information from my database is by writing a query or using a filter. Writing a query is time consuming and not fit for outside users. Using filters may be right for me but the output is not in the format I want. Here is why:

The table that I am filtering is large, many subforms. I can filter by form very nicely to find particular records, but the entire record is displayed. I would like an output in tabular form only showing those elements that have been searched for in addition to whatever elements the user chooses to show. I would also like the user to have the ability to maintain the use of basic functions in their search. For instance, display companies only with employees greater than 100,000. Basically I want the ease of the filter to accomplish the task of a query (with a form output). It would be nice to automatically force the output of a filter into a form "template."

I have begun looking at data access pages as a possible solution. Will this provide me with the capabilities I am looking for? Perhaps the better question is, can anything provide the capabilities I am looking for? The ideal solution would be an easy information recovery tool that someone with no knowledge of Access can use.

Thank you for any assistance you can provide.

Hello all

Hope you may be able to help! Does anybody know if it is possible to have a form in MS Excel or Word, which one can use to search for and display data from an Access database? I cannot just use Excel, as Excel only supports 65k rows, and I have over £120K records - and sadly splitting the database info into two is not an option! I need to use word or excel as a front end interface due to license issues, and the amount of users that require access the info in the database means it will be very expensive to buy an Access license for all users.

Ideally, I would like to have a form in Excel or Word, where I could search through the primary key field in a database, and then pull through / display the record that relates to that primary key. Ultimately, the Word/Excel document would just act as front end interface, pulling

A tall order? I hope not! I have some knowledge of VB, and have tried to play around with the DDE functionality in MS applications, but have not been all that successful!

Any help would be greatly appreciated!


Hello, I have just used the SQL Server Migration Assistant 2005 for Access (SSMA) to convert all of my Access tables into linked tables in a SQL Server 2005 database.

Everything appears to have worked fine, but now when I open any form I get the following error.

ODBC--Cannot lock all records.

If I click OK the form loads and everything appears to work OK, but the error is annoying and I want to get rid of it. Doing a Google search people have suggested going into the ToolsOptionsAdvanced menu and setting Default Open Mode to Shared and Default Record Locking to No Locks. Unfortunately this made no difference.

I thought the problem might be in my On Load code for the form and put in a break point, but the error appears immediately before the breakpoint is triggered, so it isnt my code thats causing the problem.

Can anyone assist in getting rid of this error?

Any help is much appreciated.

I have not created a database from the ground up in a few years but am familiar with using access and the general methods. What I am trying to create is a database with data on a number of "Owner/Clients", all the "Vessels" each client owns, the "Vessel" details such as type of vessel, price etc., and then a final table on the "Policy Details" for each individual vessel. I have a good idea of the 4 tables I definitely (Owner Table, Policy Table, Product Table, and Vessel Table) but I am not sure what I need to set as primary, and foreign keys to keep all the data properly linked. The end result I am looking for is a main database table where we can type in for example a specific Vessel Name, or Owner Name and it would display all of the vessels owned by that owner and than all the corresponding information for each vessel. I have attached an excel sheet that displays a diagram of what I would like to build and an ideal end result. Any advice on how to set up these tables and or keys would be greatly appreciated as I feel I can build the database once I know what direction to start in with the tables. Thank you all in advance for taking the time to read this and for any help you can provide.

Hello there, I am needing some help with barcodes and our access database 2003.

We are a small company and process approx 15000 orders a year to individual companies.

When an order is ready a mailing lable is printed and it is updated as complete on the database. Using a customers name or order number can take a while to search for them, therefore I would like to use barcodes (and would expand them to do more work for us).

My problem is I have been trying to use code 39 barcode fonts and use the customers order number to generate the barcode but at the moment I can't get that to work as there needs to be an *asterisk each end of the customer number for the barcode reader to scan properly, it might seem daft but to enter an asterisk at each side of the number is a bit of a pain, not only for backtracking through current orders but also people forget and it won't scan.

Is there a better type of barcode to use that will avoid this? I have tried a few barcode generating plugins but don't want to commit to one (ie pay for a full version) until I know it works.

When scanning the barcodes I am usuing a lookup form and the scanner shows the number and the the form searches, ideally poping up the customers order in form layout. I have no code writing experience, again whats the most efficient way of updating todays date quickly into the correct field on the customers order.

Any help much appreciated.

Hello friends, I have searched and searched and can't seem to find a simple answer for my question. I have a form in which i want to place a command button that will simply print a pdf stored on my computer. I want it to simply print it without leaving acrobat reader open etc. I don't want to have to choose printers or other options. Just simply send the pdf to the default printer. I am using access 2000. Thanks in advance. and p.s., I hope I'm not asking in the wrong section. I can't seem to figure out where this falls )

I have used the search and it proved to be very helpful, as always. However, I still have an issue with my coding, it keeps trying to open my Excel file I have imported after I have closed it (or at least I think I have). Here is the code (copied right from another thread with a few tweaks):

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Sub Command0_Click()
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim oApp As Object

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
sFilter = "acSpreadsheetTypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Select the Information to Import"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Exit Sub
End If

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile

With oApp
.Visible = True
WrksheetName = "Import"
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, WrksheetName, OpenFile.lpstrFile, True
End With

Set oApp = Nothing

End Sub

Hi All - i'm not very good at the basic structuring of queries/sql.... i want to check for 2 conditions, and do some maths depending on the condition. So, in my head, i'd just use an AND/OR condition, but it doesn;t work.

I'll not paste the complicated code because i'm looking for an overview of how i should structure it, basically if A is like "this" then select this stuff, or if A like "that" then select other stuff...

Do i need a 'union' query, and if so, how does that work with the conditions ?

	SELECT ([NOTES]*10) AS Chrometime, 
FROM [jb-2001]
WHERE ([NOTES],4) Like "THIS") 


SELECT ([NOTES]*5) AS Chrometime, 
FROM [jb-2001]
WHERE ([NOTES],4) Like "THAT") ;

I've been given this job to do at work where I have to steamline this database (a flat table created by someone else) so that I can automatically export Standardised data to an all singing and dancing SAP system. I have done all the relatively hard work (with a little help from this Forum I might add)

OK - I work at a University we have two campuses and for each campus we have different places for students to live and these are (were) stored on my flat table database - I have a form (ExportForm) with a Combo Box for Location (ie. Campus 1 or 2) and a List box for AllocatedHall (i.e the residence).

Firstly can the List Box be updated as it is? (I believe the answer is no)

So I've made two new related tables:

tbl1 is Location and contains the fields: LocationNo & Location
tble is AccomodationLIst and contains: LocationNo, AccomodationNo, AccomodationCode, AccomodationName

there is a one to many relationship between LocationNo etc etc

On the form the source table is my flat table

The Combo box has the requery code:

Private Sub Location_AfterUpdate()
End Sub

and as the row source the query:

SELECT [Location].[LocationNo], [Location].[Location]
FROM Location;

The List Box has the following as the Row source the query:

SELECT [Accomodation_List].[LocationNo],
[Accomodation_List].[AccomodationNo], [Accomodation_List].
[AccomodationCode], [Accomodation_List].
FROM Accomodation_List
WHERE ((([Accomodation_List].[Accomodation_Name])

When i try to get this to work I get blank in the AllocationHall list

Please help I have been pulling my hair out over this for too long - i have lookedat loads of different ways around it and they all keep doing the same



I know that Google can be man's best friend in such a situation, but was wondering if anyone can help me more here.

I am looking for some ideas on how to build a form which searches for values as given by a user. E.g. this form has maybe 10 text boxes for search criteria, which can be connected with "AND" and "OR". The results are then given in a list box.
(B.T.W. I have seen something that sorta matches this description on the Access Web under "Forms" )

It would also be great if some kind of functionality was built in, that allowed the user to print a report based on the search results, by the click of one button.

Any ideas would be very appreciated.

Kind Regards,


Iím quite new to Access and Iím in the middle of implementing an invoicing system for a user. Iím struggling to create statements for each of the customers. Basically, I have created a form (StatementDate) which contains two unbound fields (StartDate) and (EndDate) and requires the user to enter dates using the date picker. Above that I have a combo box (Combo10) with the customers from which the user has to pick which customer heíd like a statement for.

Using this, he can then create a report (Statement01) which will bring up the customerís invoices between the two chosen dates.

However, this is where I struggle. I have created the report with the customerís details and the invoice details but I need a command button to open this report with the details in. The report is based on a query (Statement1). How do I go about doing this? What code would I need?

Am I doing this correctly, or is there an easier method to create a statement for the customers?

Please help.

I hope this doesnít sound confusing and Iím sorry if I sound a bit dumb too.

Thank you,


is it possible to write a code in vba to do the following:

MappingID Autonumber
Svc_code Text
Svc_descr Text
Svc_accountID Number
Svc_companyID Number

InvoiceID Autonumber
Svc_companyID Number
App_number Text
App_lname Text
App_fname Text
Svc_code Text
Svc_descr Text
Svc_fee Number

take every Svc_code & svc_descr in tblInvoices, search through tblMapping

If tblinvoices.svc_descr is null
then print tblmapping.svc_accountID for all tblmapping.svc_code=tblinvoices.svc_code where tblinvoices.svc_descr is null
print tblmapping.svc_accountID for all tblmapping.svc_code=tblInvoices.svc_code AND tblmapping.svc_descr=tblinvoices.svc_descr

If tblinvoices.svc_code is null
then print tblmapping.svc_accountID for all tblmapping.svc_descr=tblinvoices.svc_descr where tblinvoices.svc_code is null

If tblmapping.svc_code=tblInvoices.svc_code
Then print tblmapping.svc_accountID

I've been trying to use SQL, but I'm just not getting it.
I think VBA has a better chance.

Any and all help would be awesome!

This is my first post to these forums, please be gentle with me. Also, I know very little about Access reporting, having avoided the topic for all these years.

My company wants to mail a listing of work sites, by state (i.e. geographic region), to all of our living clients. This is important because most of our clients will be dead within a year and it is time sensitive information. Their perusal of the list could result in additional income for them and/or their heirs at a time when they need it most.

Our site list is approximately 125K records. Additionally, the "man" (aka the president of my company) wants to send the list on paper, since many of our clients will be unable to get out of bed to go to a computer. Additionally, the "man" wants to segregate the report by state, only sending the list for the states a client has been to. Also, realize the clients are old and may have vision problems or may have problems holding reams of paper for long periods of time.

I had recommended that we send our data set out to a printer and have them do the work. I think that was kicked out because of the confidential nature of our data set.

I have been experimenting with Access' built in reporting and have come across the following issues:Performance...although the underlying query returns quickly within SQL Analyser/Access, it takes forever to generate a report on our largest state (California). During that time, my PC slows to a crawl. Field wrapping...Some of our site names are quite long, added to the fact that I'm concatenating several fields to comprise the name. As such, the names run off the edge of the page/column, causing lost data when using Access reporting. This is the problem that Excel solves by using the "Wrap Text" formatting property. I'd like to use something like that from within Access. Number of Columns...(not database fields, columns on a page) when using the Access tools, seems you can only have either 1 or 2 columns on a page and that the layout of the page is inflexible (or, more likely, escapes my ability to understand it). To save paper and reduce the weight of the paper being held by our clients, I have decided to split the report into 2 columns...though Access reporting seems to kind of do this, it doesn't seem repeatable/customizable. I need something like Publisher's links, in conjunction with Excel's wrap text to make this work right. If it weren't for the column limitation, I would just use Excel and be done with it. Is there any way to configue Access reporting (or even Excel) to do this? Cell shading...I'd like to shade every other entry, like the cell shading in Excel, to help the client understand which row they're on. Any way to do this in Access reports?
Any advice anyone can give me on flexible pagination of large data sets as described above will be welcome and praised! I'm not opposed to using different tools, coding, etc. to get this done before the clients no longer need it.

One more requirement...I cannot do large amounts of this by hand, it needs to be reproducible by a non-IT person, after the initial setup.

Not that it matters much, but to be thorough:

There is an index on State.

Sample data:
ID, SiteName, City, State
1, My first site, Fresno, CA
2, My second site, San Francisco, CA
3, My third site, Tampa, FL
4, My fourth site, Dallas, TX
5, My fifth site, London, England
...etc...do this another 124,995 times.

select Sitename + '--' + COALESCE (City, 'Unknown') + ', ' + State
from vMySiteDataSet
where State = 'CA'
order by City, Sitename

This query returns over 11K results in under 5 seconds. My Access report takes over 25 minutes (possibly longer, I don't pay attention...with 100% cpu utilization) to present it incorrectly.

Please to help me! This is not classwork, it is a legitimate professional request for assistance from a contributing member of this community.

I am simply trying to create a very basic access database for my brothers business.

Basically, we have a list of employees, and my sister in law would like to have a basic pay slip where she:

1. Chooses the Employee
2. Enters the Gross amount, tax deductions and hours they work, calculated each week so no tax etc calcs required
3. Selects to preview a "page" which can be give to the employee.

OK so I have some of the aspects right but the report page I cannot nail.

The hours entered display multiple times and I cannot get the address for the employee showing (I just get nothing).

Where am I going wrong?

I have attached a zip file of the access 2007 database if you have time to have a look.

Thanks in advance


hi everybody

i ned to make a reports record source and the control sources for a few text boxs a bit more dynamic,

i have a report with 27 txt boxs going accross, the control source for these will change as they are named after dates, depending on a value set in a form.. they are named w1 to w27 .. w1 will ALWAYs be the value in the txtbox startgantt on the form, w2 will be the same value + 7 , and w3 will be the same value + 14 etc, so they each box is an increment of one week, so the control source for w1 would be for example 15/11/2010 and the control source for w2 would be 22/11/2010 and so on and so on in 7 day increments. the labels would just be a copy of the control source, the date

ive wrote some quick code but i never usualy use reports and im a bit lacking in experience in their use.. the code i wrote is quick and simple but i think should change the forms record source and should change the control sources and labels for the 27 txt boxs.. it obviously doesnt work

can somebody help ?

thanks in advance for any advice

	Private Sub Report_Open(Cancel As Integer)
Dim stSQL As String
Dim dCdate As Date
Dim count As Double
dCdate = [Forms]![Form1]![ganttstart]
stSQL = "Select [fldTrainee], [fldCourse], [" & dCdate & "]"
Me.w1_Label.Caption = dCdate
For i = 1 To 26
dCdate = dCdate + 7
stSQL = stSQL & ", [" & dCdate & "]"
Next i
stSQL = stSQL & " From qryGantt"
Me.RecordSource = stSQL
dCdate = [Forms]![Form1]![ganttstart] + 7
Me.w2_Label.Caption = dCdate
Me.w2.ControlSource = dCdate
dCdate = dCdate + 7
Me.w3_Label.Caption = dCdate
Me.w3.ControlSource = dCdate
dCdate = dCdate + 7
Me.w4_Label.Caption = dCdate
Me.w4.ControlSource = dCdate
dCdate = dCdate + 7
Me.w5_Label.Caption = dCdate
Me.w5.ControlSource = dCdate
dCdate = dCdate + 7
Me.w6_Label.Caption = dCdate
Me.w6.ControlSource = dCdate
dCdate = dCdate + 7
Me.w7_label.Caption = dCdate
Me.w7.ControlSource = dCdate
dCdate = dCdate + 7
Me.w8_Label.Caption = dCdate
Me.w8.ControlSource = dCdate
dCdate = dCdate + 7
Me.w9_Label.Caption = dCdate
Me.w9.ControlSource = dCdate
dCdate = dCdate + 7
Me.w10_Label.Caption = dCdate
Me.w10.ControlSource = dCdate
dCdate = dCdate + 7
Me.w11_Label.Caption = dCdate
Me.w11.ControlSource = dCdate
dCdate = dCdate + 7
Me.w12_Label.Caption = dCdate
Me.w12.ControlSource = dCdate
dCdate = dCdate + 7
Me.w13_Label.Caption = dCdate
Me.w13.ControlSource = dCdate
dCdate = dCdate + 7
Me.w14_Label.Caption = dCdate
Me.w14.ControlSource = dCdate
dCdate = dCdate + 7
Me.w15_Label.Caption = dCdate
Me.w15.ControlSource = dCdate
dCdate = dCdate + 7
Me.w16_Label.Caption = dCdate
Me.w16.ControlSource = dCdate
dCdate = dCdate + 7
Me.w17_Label.Caption = dCdate
Me.w17.ControlSource = dCdate
dCdate = dCdate + 7
Me.w18_Label.Caption = dCdate
Me.w18.ControlSource = dCdate
dCdate = dCdate + 7
Me.w19_Label.Caption = dCdate
Me.w19.ControlSource = dCdate
dCdate = dCdate + 7
Me.w20_Label.Caption = dCdate
Me.w20.ControlSource = dCdate
dCdate = dCdate + 7
Me.w21_Label.Caption = dCdate
Me.w21.ControlSource = dCdate
dCdate = dCdate + 7
Me.w22_Label.Caption = dCdate
Me.w22.ControlSource = dCdate
dCdate = dCdate + 7
Me.w23_Label.Caption = dCdate
Me.w23.ControlSource = dCdate
dCdate = dCdate + 7
Me.w24_Label.Caption = dCdate
Me.w24.ControlSource = dCdate
dCdate = dCdate + 7
Me.w25_Label.Caption = dCdate
Me.w25.ControlSource = dCdate
dCdate = dCdate + 7
Me.w26_Label.Caption = dCdate
Me.w26.ControlSource = dCdate
dCdate = dCdate + 7
End Sub


I have two queries that contain a CUISP (similar to a ticker, like for a stock) and the number of holders. My two queries get their data from two different databases. I need a way to add these together. I wrote a function using Dlookup, but I won't always have the same CUSIPS in each table. If I don't, DLookup returns a null value and it won't let me proceed. How can I make this work? Here is the code:

GetNumberOfHolders = (DLookup("CountOfAccount_ID", "qryCount400Accounts", "CUSIP_ID = '" & CUSIP & "'")) + (DLookup("CountOfAccount_ID", "qryCount300Accounts", "CUSIP_ID = '" & CUSIP & "'"))

Can someone suggest a way to do this?



I performed a quick search and didn't find anything to help, so if there is a previous posting that addresses this, I apologize for not finding it.

My issue is I have built an Access database with numerous VBA code calls and objects. I also reference several libraries, as you might've guessed. Not least of which is the Microsoft ActiveX Data Objects Recodset 2.7 Library - MSADOR15.DLL. However, when I install the databse on another machine, this library is unfound and error ocurs upon first call to a function in this library.

So, I can copy the library file to the new machine and then link the reference to the copied library file to make everything work, but I certainly don't want to have to walk people through having to do that on every machine to which I distribute the database.

I've checked a couple articles, including "Access Reference Problems" (http://members.rogers.com/douglas.j....nceErrors.html) and several of the articles referred to within, but cannot find anything concrete. The closest solution is that one of them says that the library file search path includes the folder in which the application resides, which in this case is Access. Unfortunately, it seems as though this could be, and likely is, operating system dependent, otherwise I'd simply copy the library to where I suspect msaccess.exe resides.

Do any of you have another reasonable solution? Or, am I simply missing something obvious (this has certainly happened before!)?

Any assistance you can provide would be most helpful.

Your humble student,

Not finding an answer? Try a Google search.