http://forums.aspfree.com/ Microsoft Access forum discussing questions and answers for the Access database. MS Access is used to handle small to medium database needs. en Wed, 19 Jun 2013 06:42:47 GMT vBulletin 60 http://forums.aspfree.com/images/misc/rss.png http://forums.aspfree.com/ http://forums.aspfree.com/microsoft-access-help-18/list-box-horizontal-bars-538611.html Mon, 17 Jun 2013 12:34:07 GMT I was hoping someone could assist me. I am having an issue getting a horizontal bar to appear in a list box on Access Form. I have tried several things and haven't been able to accomplish it. Right now I have my column count at 1 and my column width at 1. Does anyone have any suggestions? ]]> Microsoft Access Help ACNI http://forums.aspfree.com/microsoft-access-help-18/list-box-horizontal-bars-538611.html http://forums.aspfree.com/microsoft-access-help-18/how-request-input-check-database-then-report-results-538603.html Fri, 14 Jun 2013 12:35:15 GMT Hey I'm trying to use Access to use a form to generate a database, use this database to check against another database, then report a list of valid records from this database. Is this possible?

So far I have created a database of materials. I have also created another database of products. Now I would like to create a form that could input multiple records into a separate inventory database (I also want this to not append, I forget the term, but clear the previous inventory database and create a new set of records) which would hold a material field and a quantity field. Then I want to check this against the products database and report this.

Is this possible? I have essentially completed this task in excel, however it's a little messy, overcrowded, shows more information than desired and a little to tedious to utilize. And I don't have too much experience with macros, but could I create a macro to complete this from the first to last step without manually running the report etc.?

Also side question, that is the least of my concerns right now, but when creating a form, can an input mask also be a type/drop down menu of records from a database?

Thanks for the help! ]]> Microsoft Access Help npc214 http://forums.aspfree.com/microsoft-access-help-18/how-request-input-check-database-then-report-results-538603.html http://forums.aspfree.com/microsoft-access-help-18/updating-access-database-limited-internet-no-lan-538595.html Thu, 13 Jun 2013 09:28:11 GMT Dear Experts, I am seriously struggling on how to design an Access Database that will be used by multiple users, on multiple computers, in multiple locations, with the ability of sharing each locations database records between multiple locations. This would be seem simple process in most... Dear Experts,

I am seriously struggling on how to design an Access Database that will be used by multiple users, on multiple computers, in multiple locations, with the ability of sharing each locations database records between multiple locations. This would be seem simple process in most places, but where this is being undertaken there is pretty much no internet (only USB dongle connection when it works) and each location DOES NOT have LAN, with users working on standalone computers.

How should I design an Access database that will allow multiple locations to send new database records / updated database records from a database to a central database location, and once centrally compiled, the ability for the central database operator to send the fully updated central database back to the other locations - with the aim of having each locations database fully updated and mirroring that of the central database.

Look forward to hearing your expert thoughts on the matter.

Best, Nathan ]]> Microsoft Access Help NathanL http://forums.aspfree.com/microsoft-access-help-18/updating-access-database-limited-internet-no-lan-538595.html http://forums.aspfree.com/microsoft-access-help-18/field-based-other-field-538593.html Wed, 12 Jun 2013 17:49:19 GMT Hello Access experts:

This is what needs to be done:

We have a data entry form built on the access db. The form has a field say "Emp Category". Its a drop down(combo box) with data like this:

Category1
Category2
Category Test1
Category Test2

I created a new field 'Status" which is also a combo box with options like "Active" and "Inactive"

What I want is that when Emp Category has string "Test" in it, then ONLY it should enable the "Status" field. Basically status applies to only "Category Test1" and "Category Test2". for other category selections, "Status" should stay disabled.

Thanks for the help! ]]> Microsoft Access Help RNG http://forums.aspfree.com/microsoft-access-help-18/field-based-other-field-538593.html http://forums.aspfree.com/microsoft-access-help-18/need-query-result-do-lookup-another-table-538587.html Wed, 12 Jun 2013 05:20:51 GMT Win7
Access 2003

I'm stumped. An existing select query of a table called "Races" works great. The query gives me the appropriate results (records) for four defined "or" criteria. One of the fields the query generates for each record (from the Races table) is "licNum" .



So far so good. I now want to the query to create an additional field for each record produced by the query.. That new field should be a field from a third table (RecentLastYear-DriverCount). That third table also as a field "LicNum" as does the Races table. (that third table is shown on the screenshot, but not yet linked.

So, how do I get the query to produce the data from the field called "RacesLastYear-driver" (in the third table) associated with the licNum results in each record of the original query?

In other words, the original query provides records, each of which has has a LicNum field. I then need to have the query report the flied called "RacesLastYear-driver" in the third table associated with that particular record's LicNum. Both tables have "licNum" fields.

Do I use an Expression, a Dlookup function, or what? When I try to join the thrid table in any manner, it try messes up the results of the initial query producing either more or fewer records than it should and then, to add insult to injury, won't even provide me that associated data I'm looking for.

Help. This must be simple, but no amount of web searching or experimentation with things has given me a clear solution. Driving me nuts.

Many thanks for any tips. ]]> Microsoft Access Help Robert_Alder http://forums.aspfree.com/microsoft-access-help-18/need-query-result-do-lookup-another-table-538587.html http://forums.aspfree.com/microsoft-access-help-18/prevent-linked-table-re-connect-message-open-538584.html Tue, 11 Jun 2013 18:29:56 GMT I have linked to an Oracle back-end to 4 tables and 1 view. On opening my db, I get a warning asking if I want to reconnect. To do so I have to setup a VPN connection only from my work laptop. I like to do my developing on my desktop with multiple monitors. How can I prevent the warnings (multiple... I have linked to an Oracle back-end to 4 tables and 1 view. On opening my db, I get a warning asking if I want to reconnect. To do so I have to setup a VPN connection only from my work laptop. I like to do my developing on my desktop with multiple monitors. How can I prevent the warnings (multiple tables) that pop up when I open the database or open different forms? ]]> Microsoft Access Help rdperkins http://forums.aspfree.com/microsoft-access-help-18/prevent-linked-table-re-connect-message-open-538584.html http://forums.aspfree.com/microsoft-access-help-18/add-fields-duplicate-records-538583.html Tue, 11 Jun 2013 16:30:38 GMT Hello again, I have a database with a table of employees. Each record has the name of an employee, their supervisor, and sales per day of that employee. The records have a field per day per employee for a month. I want to show the total sales made (add all of the sales data from multiple... Hello again,
I have a database with a table of employees. Each record has the name of an employee, their supervisor, and sales per day of that employee. The records have a field per day per employee for a month. I want to show the total sales made (add all of the sales data from multiple fields) based on the supervisor (multiple records with the same supervisor). How can I do this?

Thanks ]]> Microsoft Access Help logitechnoob http://forums.aspfree.com/microsoft-access-help-18/add-fields-duplicate-records-538583.html http://forums.aspfree.com/microsoft-access-help-18/data-replaced-characters-query-results-538559.html Mon, 03 Jun 2013 22:34:37 GMT I'm encountering something I've never seen before. I have a SQL UNION query joining two access queries. One column of data is being replaced by characters in the results (an asterisk, an ampersand, a little square with a question mark in the middle, open parenthesis, underscore, bracket etc.) instead of numbers. It looks kind of the way it would in Word if the language was changed to something weird and the letters changed to pictures and Cyrillic characters or something. But the queries that feed into the union query are fine. I've rebuilt the union query and checked everything I can think of. Has anybody else ever encountered this before? ]]> Microsoft Access Help CMB http://forums.aspfree.com/microsoft-access-help-18/data-replaced-characters-query-results-538559.html http://forums.aspfree.com/microsoft-access-help-18/access-beginner-help-queries-538555.html Sun, 02 Jun 2013 11:40:20 GMT I have many tables with the exact same columns of which a couple are 'lot number' and 'qty used'. I need to to find the total used of each different lot number even though there are many lot numbers in the field over all the tables. I also need to do this over 9 groups over all the tables. Will a duplicate entry query work???? Sorry I am just learning Access....... ]]> Microsoft Access Help Karl Neil http://forums.aspfree.com/microsoft-access-help-18/access-beginner-help-queries-538555.html http://forums.aspfree.com/microsoft-access-help-18/help-subscription-list-538554.html Sat, 01 Jun 2013 20:29:38 GMT Hello!

I'm a basic Access User and I've offered to help out a friend who owns a comic book shop. However, I've hit a snag and can't figure out how to make this work (hopefully it's something simple that I'm overlooking)

Essentially, customers give the shop owner a list of what comic books they want every month. He has less than a hundred customers but over 1000 titles a month.

I figured two tables:

Customer Table
-ID #
-Last Name
-First Name
-E-Mail
-Phone

Comic Book Table
-ID#
-Title
-Publisher

I know how to do relationships, but what I can't figure out is just where/how to put the customer's individual comics so that the owner can produce two basic reports:

Customer Report that alphabetically lists each customer by last name with the list of books they want to get
Distribution Report that lists each comic book title and the customers who wish to have this book set aside for them

Any suggestions would be greatly appreciated!

Thank you,

SG ]]> Microsoft Access Help SGhost http://forums.aspfree.com/microsoft-access-help-18/help-subscription-list-538554.html http://forums.aspfree.com/microsoft-access-help-18/format-subform-538550.html Fri, 31 May 2013 09:46:23 GMT Is it possible to show a subform as a single form format when the recordsource is a query? I can set it up to show everything in datasheet format, no problem, but when I select the single form format I get a blank screen. Any suggestions? Anyone... Is it possible to show a subform as a single form format when the recordsource is a query?

I can set it up to show everything in datasheet format, no problem, but when I select the single form format I get a blank screen.
Any suggestions? Anyone... ]]> Microsoft Access Help Anathil http://forums.aspfree.com/microsoft-access-help-18/format-subform-538550.html http://forums.aspfree.com/microsoft-access-help-18/new-record-538549.html Thu, 30 May 2013 23:38:00 GMT I created a small database with lots of buttons leading to forms that are attached to the primary record called "intake". When I create a new intake, I have to close and reopen the database for the buttons to recognize the new record. How can I fix this? ]]> Microsoft Access Help jwhiteford http://forums.aspfree.com/microsoft-access-help-18/new-record-538549.html http://forums.aspfree.com/microsoft-access-help-18/access-form-behavior-problem-538547.html Thu, 30 May 2013 02:50:23 GMT Hello,

I am fairly new to Access and I have run across an issue that I cannot figure out.

I have a form where each record contains a textbox for a certification and an expiry date. It also contains an unbound textbox with Default Value = [Expiry Date].
(I had a more complicated expression but I simplified it to get to the root of the problem)

The problem I am having is that on each record the expression evaluates to the result from the first record.
It looks something like:

WHMIS 12/17/2013 12/17/2013
First Aid 1/13/2014 12/17/2013
Fork Lift 2/12/2015 12/17/2013
and so on (where the last textbox is the calculated one)

Any help or suggestions would be greatly appreciated.
Thanks ]]> Microsoft Access Help MVuorela http://forums.aspfree.com/microsoft-access-help-18/access-form-behavior-problem-538547.html http://forums.aspfree.com/microsoft-access-help-18/trouble-find-duplicate-query-calculated-field-538546.html Wed, 29 May 2013 19:30:27 GMT I'm having trouble creating a Find Duplicates query that will return duplicates for a calculated field.

My first attempt was to create one query to calculate the field and then use the Wizard to create a Find Duplicates query based off of the first query. Here's the SQL for both:

1) to create qry2012PubsFixes:
SELECT tbl2012Pubs.ID1, tbl2012Pubs.Title, Replace(LCase([Title]),".","") AS TitleFix, tbl2012Pubs.DOI
FROM tbl2012Pubs;

2) to create the Find Duplicates query:
SELECT qry2012PubsFixes.TitleFix, qry2012PubsFixes.ID1, qry2012PubsFixes.DOI
FROM qry2012PubsFixes
WHERE (((qry2012PubsFixes.TitleFix) In (SELECT [TitleFix] FROM [qry2012PubsFixes] As Tmp GROUP BY [TitleFix] HAVING Count(*)>1 )))
ORDER BY qry2012PubsFixes.TitleFix;

The error message I get when trying to run the second query is: "You tried to execute a query that does not include the specified expression 'Count(*)>1 And Replace(LCase([Title]),".","")=' as part of an aggregate function.

I've tried to build a single query pulling from tbl2012Pubs that does both the calculation and displays the duplicates:
SELECT tbl2012Pubs.ID1, tbl2012Pubs.Title, Replace(LCase([Title]),".","") AS TitleFix, tbl2012Pubs.DOI
FROM tbl2012Pubs
WHERE (((TitleFix) In (SELECT [TitleFix] FROM [tbl2012Pubs] As Tmp GROUP BY [TitleFix] HAVING Count(*)>1 )))
ORDER BY TitleFix;

The problem here is that I get 3 different TitleFix columns in Design View - one that does the calculation, one that is sorted by, and one that seems to apply to the WHERE clause. The latter 2 are of course blank, so I'm asked to enter a parameter value for them when I try to run the query. I've tried subbing in 'Replace(LCase([Title]),".","") AS TitleFix' in various places in the WHERE clause, but keep coming up with syntax errors, so I'm not sure that's the right way to go.

I suspect I need a subquery or possibly a CROSS APPLY, but I just don't have the SQL chops to pull this off myself. Any help would be most excellent!

(Using Access 2010, 64 bit PC.)

Thanks! ]]> Microsoft Access Help bcarothers http://forums.aspfree.com/microsoft-access-help-18/trouble-find-duplicate-query-calculated-field-538546.html http://forums.aspfree.com/microsoft-access-help-18/ms-access-active-directory-check-see-if-email-address-exists-538541.html Tue, 28 May 2013 19:07:31 GMT Hello, I have a small MS Access DB with a table that is storing names and email addresses for users. Trying to get my head wrapped around how I would compare a field and its values stored in the MS Access table with a similar field and values in another database and then display the results on a... Hello,

I have a small MS Access DB with a table that is storing names and email addresses for users. Trying to get my head wrapped around how I would compare a field and its values stored in the MS Access table with a similar field and values in another database and then display the results on a webpage (nested loops?). I am not having any problems querying the MS Access or the Active Directory and displaying results on a webpage but definitely not understanding how to compare and only display the names of the users in the MS Access database whose email address can be found in the Active Directory.

Hope I posted this in the right forum and my request makes sense. Any help greatly appreciated.
gully

Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
Sql = "SELECT name, email FROM Person"
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("***.mdb")
Rs.Open Sql, Conn, 0, 2

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Provider = "ADsDSOObject"
strConn = "Active Directory Provider"
objConn.Open strConn
strQuery = "SELECT mail FROM 'GC://****'"
objRS.Open strQuery, objConn, 1, 1 ]]> Microsoft Access Help gully53 http://forums.aspfree.com/microsoft-access-help-18/ms-access-active-directory-check-see-if-email-address-exists-538541.html