Show Contact Name Unique To Supplier

Jan 26, 2005

I have been plodding along slowly building up a small DB and have hit a slight snag (or 2).

I have a Supplier Form (frmSuppliers). I have a frmSupplierContacts.
I want to prevent users entering data in the standalone frmSupplierContacts?

Also on frmPurchaseOrders I have a combo ContactID. I want the combo to display Supplier Contact Names for the selected supplier only.

I have put as row source:

SELECT tblSupplierContacts.ContactID, [LastName] & ", " & [FirstName] AS Contact FROM tblSupplierContacts WHERE (((tblSupplierContacts.SupplierID)=Forms!frmPurcha seOrders!SupplierID)) ORDER BY [LastName] & ", " & [FirstName];


This displays a Contact Name, but does not change if you select a different supplier?

Hopefully attached DB will explain all.
Thanks for any help in advance.
Phil.

View Replies


ADVERTISEMENT

Send E-mail To Supplier Using Supplier Field

Feb 29, 2008

hi,

at present i have an order form. this form has a id number and supplier field . at the moment a macro runs a report which produces the order. the macro does ask me for the order number. the order number is the id number, what i would like to do is get a macro that looks at the id field and supplier field, fetch the e-mail address from the supplier table and send the order to the e-mail address

thanks
steve

View 1 Replies View Related

Show Records With Unique Field

Jun 8, 2006

Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.

What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.

I've attached a screenshot and here is the sql:
SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. *
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"

I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.

Thank you,
RCurtin

View 1 Replies View Related

Forms :: Can't Get Unique Values To Show In Combo Box

Jul 22, 2014

I've used this guide [URL] .... to only show unique values in a combo box. I can't get it to work, it just shows a load of blank values. If I run the expression builder part from the background, it works but it doesn't work in the combo box. Not sure what I can post to give more details?

View 13 Replies View Related

Queries :: Excluding Duplicates - Show Only Unique Values

May 17, 2013

I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...

View 3 Replies View Related

Queries :: Manifest - Query A List And Then Show Only Unique Values

Oct 1, 2013

One Manifest can have many line items. One line item can only have 1 designation. Each manifest may have many line item with same designations.

For example:
Manifest #0001
Line Item 1: N
Line Item 2: H
Line Item 3: U
Line Item 4: N
Line Item 5: P
Line Item 6: H

Table set-up
CurrentCY
CurrentCYIDPK
WasteCategoryIDFK
LineItemInformation [Line Item 1, Line Item 2, etc]
ManifestDataIDFK

ManifestData
ManifestDataIDPK
ManifestNumber [0001]

WasteCategory
WasteCategoryIDPK
WasteCategory [H, N, U, etc]

I'd like to query the line items for each manifest so the end result has the manifest number (Manifest #0001) in a field and the designations (N, H, P, U) in fields on a report.

View 14 Replies View Related

Queries :: Select Distinct For Combobox - Show Only Unique Values

Oct 20, 2014

I am trying to get a combo box to only show unique values, but I can't get it to work properly - it still shows duplicates in the list.

The Row Source is set to

SELECT DISTINCT Areas.ID, Areas.Area FROM Areas ORDER BY Areas.Area;

And the combo is bound to column 2

View 12 Replies View Related

Customer & Supplier - What If They Are Both?

Aug 14, 2006

Currently my design consists of a table for customers and a table for suppliers. At first it seems logical. Or so I thought!

As my design is evloving I have noticed a problem. What if a supplier calls and says "I want to purchase a..." they then also become a customer!

Obvoius I know but do I leave the design (attached) as is or disolve the supplier table and then just add one field called [CustSupp] and have three options to choose from "Customer", "Supplier" or "Both"

I could then base any other forms like the [FrmCustomers] on a query that looks at the [CustSupp] field and has a Like "Customer" criteria

Whst is the best method. Any advice greatly received.

View 12 Replies View Related

Queries :: Field Supplier With More Than One Category

Jan 13, 2015

I've got a table called invoice with a field supplier and another one invoice_category among others.

How can I retrieve all the suppliers that have more than two categories in the invoice table.

I tried

Code:
select count(*) from (select distinct invoice_category from invoice)

but it didn't work.

View 2 Replies View Related

Reports :: Email Report Based On Supplier?

Apr 14, 2014

I have a report that creates purchase orders, this could be many suppliers these suppliers have e-mail addresses.

is it possible to get the report to look at the supplier name , find the e-mail for that supplier and send it to them.

View 1 Replies View Related

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

Aug 1, 2014

I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:

Start Date/Time
End Date/Time
Employee

At the moment this is what the format of my report looks like (I removed other unnecessary fields):

StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe

I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?

View 2 Replies View Related

Contact Help

May 1, 2007

Good afternoon All,
I know there is a way to do what I need, but I am a bit rusty with my Access skills. Any help is greatly appreciated!

Here is what I would like to do as an end result...
When my users enter a name on my form, I would like for the address and company information for that person automatically fill in. If they are not already in the contacts list, I would like for the record to be added. Any idea how I accomplish this?

So far I have the tblAccts (which would be the main information entered on the form), tblContacts (where the names, companies,and contact information is stored), and frmAcctOpen (the user's form for opening accounts).

It has been about 6+ years since I have really messed around with access, so please speak basic language in your answers.

Thank you!

View 3 Replies View Related

Add New Contact Name

Feb 8, 2005

Hi Guys. I have a form 'frmPurchaseOrders'. The user selects a Supplier from a combo. The form contains a control 'ContactID'. I want the user to be able to add a new Supplier Contact. I have added a cmd 'cmdNewContact'. When clicked I want the Supplier form to open on the relevant supplier. I added this code to the cmd:


On Error GoTo Err_cmdNewContact_Click

Dim strDocName As String

strDocName = "frmSuppliers"

DoCmd.OpenForm strDocName, , , [SupplierID] = [Forms]![frmPurchaseOrders]![tblSuppliers.SupplierID] 'I have 2 supplier ID's on the form
DoCmd.Close acForm, "frmPurchaseOrders"

' Give CompanyName control focus.
Forms![frmSuppliers]!CompanyName.SetFocus

Exit_cmdNewContact_Click:
Exit Sub

Err_cmdNewContact_Click:
MsgBox Err.Description
Resume Exit_cmdNewContact_Click


frmSuppliers opens but not on the desired record (where supplier id equals the suppliers id of the purchase orders form).
Any ideas?
Cheers,
Phil.

View 5 Replies View Related

Contact Help

May 2, 2007

Good afternoon All,
I know there is a way to do what I need, but I am a bit rusty with my Access skills. Any help is greatly appreciated!

Here is what I would like to do as an end result...
When my users enter a name on my form, I would like for the address and company information for that person automatically fill in. If they are not already in the contacts list, I would like for the record to be added. Any idea how I accomplish this?

So far I have the tblAccts (which would be the main information entered on the form), tblContacts (where the names, companies,and contact information is stored), and frmAcctOpen (the user's form for opening accounts).

It has been about 6+ years since I have really messed around with access, so please speak basic language in your answers.

Thank you!

View 1 Replies View Related

Query By Contact

Aug 2, 2007

Hi all,

I've written a query in SQL View. To be honest, its my first SQL Query EVER and I'm very happy that it works for half of what I need to do.

I'll paste the query here:

SELECT DISTINCT
Abs(Avg([Handoff Tracking].[Internal Kickoff?])) AS [Avg Of Internal Kickoff?],
Abs(Avg([Handoff Tracking].[E-Mailed prior to visit?])) AS [Avg Of E-Mailed prior to visit?],
Abs(Avg([Handoff Tracking].[Call to Support?])) AS [Avg Of Call to Support?],
Abs(Avg([Handoff Tracking].[Transfer to Support?])) AS [Avg Of Transfer to Support?],
Abs(Avg([Handoff Tracking].[Recieve Post trip Info?])) AS [Avg Of Recieve Post trip Info?],
Abs(Avg([Handoff Tracking].[Total Workstations])) AS [Avg Of Total Workstations],
Abs(Avg([Handoff Tracking].[Failed workstations])) AS [Avg Of Failed workstations],
Abs(Avg([Handoff Tracking].[Config File Issues?])) AS [Avg Of Config File Issues?],
Abs(Avg([Handoff Tracking].[3rd Party App Issues?])) AS [Avg Of 3rd Party App Issues?],
Abs(Avg([Handoff Tracking].[Welcome Pack Reviewed?])) AS [Avg Of Welcome Pack Reviewed?]
FROM [Handoff Tracking];

As you probably see above it does the average of [table name].[field name] then outside THOSE brackets it does the absolute value of that averaged value (the numbers USED to show up as negative numbers.

This query is great becuase it checks the average value of ALL records regardless of contact, which is good becuase I need something to compare each contact to.

Now I need to get a query that will do the average value, for all fields PER contact!

What would be the string to narrow the range of the average equation down to a per contact basis.
As far as relationships go I have 2 tables 1 for contacts.... and 1 for handoff tracking very simple DB structure wise. I just need to know how to do the same query as above, on a per user basis. Thanks for any input anyone might have :)

View 1 Replies View Related

Send Email Via Contact DB

Sep 25, 2005

I have a contact database which store email address and other info for customers. I have searched and searched. How can I use these email links to open and email to the contact? Such as how to email you@there.com from DAP or Form? So the user can click on the email address and auto open Outlook to send that contact an email? This is so much easier in webpage building :o Any direction would be greatly appreciated. Thanks guys :)

View 2 Replies View Related

Look Up Query In Contact Database

Dec 1, 2006

I have a database with all of my contacts... I have a report that shows my contacts in alphabetical order and I have a form for adding new contacts. I was wondering if there is a way to have a report or query that will look up a contact by typing in the person or the company name.. Is this possible and if so.. how do I do this?

View 1 Replies View Related

Add Photo In Contact List

Jul 16, 2006

Hi all, I have a form of contacts wich I would like to put a photo for every contact, I would like to have a "ADD" button on the form wich will add me a photo in JPG or BMP in a certain field on the form, the photo ar stored in a folder in c: I also would like to have a "DELETE" button!!!
Thanks to all
Marco

View 1 Replies View Related

Contact Management Database NEED HELP

Oct 30, 2006

Hi,

I'm new to this forum so if I ask something stupid please forgive :)

I want to do something like the contact management database which is offered by microsoft for download.

I played a little with it and my problem is I copy-pasted the "calls" tab and on the pasted tab when I browse throug the different calls the call notes field below wont change / update. I thought there is maybe a macro behind it but I looked at every macro and did not find anything appropriate in my opinion.

It is maybe only a minor but I can not figure it out,

Thanks for any help in advance,

Leslie

PS.: I uploaded the database to rapidshare : http://rapidshare.com/files/1305209/Contact_management_database.mdb

View 14 Replies View Related

Add Contact From Outlook In A Subform

Aug 29, 2014

I have a form that uses the command AddContactfromOutlook. I used the standard example given by Microsoft and it works flawlessly in the form. When I use the form as a subform this command button breaks.

View 3 Replies View Related

Trying To Customize The Sample Contact Management DB

Jun 20, 2005

Hi,

I need to customize the sample Contact Management DB (CONTACT) that comes with MS Access 2000.

I removed the field ‘Region’ in the ‘Company’ table, in the ‘Company’ form, and in the ‘ContactAddress’ query, respectively. Now, when I try to access the ‘Enter/View Contacts’ form through the Main Swithboard's button, it shows a small warning windows asking to ‘Enter Parameter Value’, referring to "Company.Region." What did I wrong and/or should I do to correct this problem?

I'll appreciate your help.

Orlando

View 1 Replies View Related

Update Contact Details Automatically

Nov 20, 2007

Dear all, please excuse the fact that this is not an explicit problem I have, but more of a general query.

I have an Access database (2003) with a load of contact details in it. Including various fields for each contact, such as their email, services they offer, where they are, main contact name etc.

Some of these contact details will be out of date and I want to make sure that all the contact details are up-to-date.

I am in the process of setting up a mail merge with a Word document which will output to Word their relevant details, and this can then be e-mailed to each of the contacts with them to update and e-mail back again. Someone would then manually update each of the contact details.

This is quite a time intensive way of doing it, and I wondered is there a way of automating the process? I don't have the time to put the whole of their details on a website so they can update it over the web, but is there a way that access could possibly read the return Word document to automate updating their details? Or is there another easier way of doing it, without using word and using something else such as Excel or something.

If anyone has any experience in doing this kind of thing I would be really glad of some tips.

Many thanks

View 9 Replies View Related

Place A Photo On A Contact Form

Jul 28, 2006

Hi I have a Contact form with all the details of the contacts, I would like to add a photo for each contact, how can I get to this?
Marco

View 1 Replies View Related

New Record For This Contact - Command Button

Sep 7, 2006

Hi all,

I have a database for customer enquiries, and there is one record per product that the customer shows interest in. I want to have a button on the enquiry details form that creates a new enquiry but automatically fills in the contact details from the previous record.

Please also note that I do not wish to "normalize" this so that there is a seperate table for contacts and another for enquiries; it's much easier from our point of view to have the whole enquiry in one record, especially as this is a stand-in database before a fully integrated CRM is implemented. In any case most of the other data (e.g. product, category etc) is already stored in separate tables.

What sort of method do I need to use to create a Command Button that:

-Creates a new record
-Copies and Pastes data from selected fields into the new record?

I tried using the command button wizard for sample code but I got paste errors which were pasted into a new table, and I don't really understand the process of how this works as the Commands are numbers from a list (apparently in the macro window), which I can't find.

I'm using Access 2003 but the database file is in A2000 format. The code is below.

Thank you!



Private Sub ContactAddEnq_Click()
On Error GoTo Err_ContactAddEnq_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_ContactAddEnq_Click:
Exit Sub

Err_ContactAddEnq_Click:
MsgBox Err.Description
Resume Exit_ContactAddEnq_Click

End Sub

View 2 Replies View Related

Updating Contact Info In Outlook

Feb 16, 2006

I have the following code working great with creating new contacts in Outlook from Access. My problem is I can't figure out a way to update already existing contacts in Outlook.

Function AddContacts()

Dim OutlookObj As Outlook.Application
Dim Nms As Outlook.NameSpace
Dim MyContacts As Object
Dim MyItems As Object
Dim MyItem As Object
Dim Db As Database
Dim Rst As Recordset

Set Db = CurrentDb
Set Rst = Db.OpenRecordset("CustomerOutlook", dbOpenDynaset)
Set OutlookObj = CreateObject("Outlook.application")
Set Nms = OutlookObj.GetNamespace("MAPI")

'to point to a nested folder in Outlook you have to create folder items in folder items:
Set MyContacts = Nms.Folders.Item("Public Folders").Folders.Item("All Public Folders").Folders.Item("Our Contacts")
Set MyItems = MyContacts.Items

While Not Rst.EOF
'Declare which form to be used to add your contacts
Set MyItem = MyItems.Add("IPM.Contact")

'add fields, to find out which fields are available take a look at all the available members of the
'ContactItem class in the Outlook object library.
'Be careful, Microsoft is using various names for outlook fields!
MyItem.CompanyName = Rst!BusinessName
MyItem.FirstName = Rst!CFirst
MyItem.LastName = Rst!CLast
If Not IsNothing(Me.WebSite) Then
MyItem.WebPage = Rst!WebSite
End If
If Not IsNothing(Me.CMiddle) Then
MyItem.MiddleName = Rst!CMiddle
End If
If Not IsNothing(Me.Suffix) Then
MyItem.Suffix = Rst!Suffix
End If
MyItem.JobTitle = Rst!JobTitle
MyItem.BusinessTelephoneNumber = Rst!Phone
MyItem.Business2TelephoneNumber = Rst!BackPhone
MyItem.MobileTelephoneNumber = Rst!Mobile
MyItem.BusinessFaxNumber = Rst!Fax
MyItem.Email1Address = Rst!Email
If Not IsNothing(Me.Address1) Then
If Not IsNothing(Me.Address2) Then
MyItem.BusinessAddressStreet = Rst!Address1 & ", " & Rst!Address2
Else
MyItem.BusinessAddressStreet = Rst!Address1
End If
End If
MyItem.BusinessAddressCity = Rst!CCity
MyItem.BusinessAddressState = Rst!CState
MyItem.BusinessAddressPostalCode = Rst!PostalCode
MyItem.Categories = Rst!CustomerType
MyItem.FileAs = Rst!BusinessName

'MyItem.Etc = Rst!Etc
MyItem.Close (olSave)
Rst.MoveNext
Wend
End Function

View 2 Replies View Related

Printing Image Contact Form

Jun 9, 2007

I created a print image form in my project so that when a user opens the form, it cycles through all the images stored in a temp folder and prints them out four images/page at a time until all the images have been cycled through. I am using the below code on the "OnCurrent" event of the form. The problem I am having is trying to close the form with code after the images have been cycled through. I get an error message stating that "this action can't be carried out while processing a form or report event" I guess it is caused by the form still being sent to print. How do I add a code to catch the event when the form has finished printing? Also, when I open the form by itself, it works correctly and prints all of the images. However, when I tried to use a switchboard to open the form, it prints the switchboard form. Please help me in correcting this. Thanks

Private Sub Form_Current()
Dim mysql As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim PicPath As String
Dim TotalSet As Integer
Dim CurSet As Integer
Dim SetStart As Integer
Dim RecNo As Integer
Dim PicNo As Integer
Dim RecCount As Integer
Dim RecNoMove As String
Dim PageCount As String


Set rs = New ADODB.Recordset
Set conn = CurrentProject.Connection

mysql = "SELECT tbl_Image.*, tbl_Image.SelectPrint " & _
"FROM tbl_Image " & _
"WHERE (((tbl_Image.SelectPrint)=Yes));"

rs.Open mysql, conn, adOpenKeyset, adLockOptimistic
RecCount = rs.recordCount

TotalSet = (Int(((RecCount) / 5)) + 1)
CurSet = 1
PicNo = 1

SetStart = (((CurSet) * 4) - 4) + 1
rs.MoveFirst

NextSet:
For RecNo = 1 To 4
PicPath = rs("myimagepath")
Me("image" & PicNo).Picture = PicPath
PicNo = PicNo + 1
rs.MoveNext
If rs.EOF Then
If Me.Image1.Picture = "(none)" Then
End
Else
PageCount = "Page " & CurSet & " of " & TotalSet
Me.PageCount_txt = PageCount
DoCmd.PrintOut acPrintAll
GoTo closepage

End If
End
Else
End If
Next RecNo
PageCount = "Page " & CurSet & " of " & TotalSet
Me.PageCount_txt = PageCount
DoCmd.PrintOut acPrintAll
PicNo = 1
CurSet = CurSet + 1
Me.Image1.Picture = "(none)"
Me.Image2.Picture = "(none)"
Me.Image3.Picture = "(none)"
Me.Image4.Picture = "(none)"
GoTo NextSet

End

closepage:
DoCmd.Close acForm, "frm_printimage_4", acSaveNo

End Sub

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved