HELP! Search and Fill

[Jeff @ CI]

In my Access 2000 database, I have four tables involved in this problem.
"Events" contains the cities where my company holds seminars. The focus
field here is called "EventCode". "Contractors" lists reps who work with us.
The Key here is a field called "ContractorID". The "Cities" table has a
list of cities and states where the reps will travel to recruit for the
seminars. This table is related to the "Contractors" table by the
ContractorID field. These three tables contain the data to be used to fill a
portion of the fields in the fourth table, "EventPerformance".

In a form, I want to select an Event and based either on a field named
"City" or another field named "State" and then search for reps who have
indicated they will work the event. From the list, I then want to be able to
click on a rep's name (the result of the search directed into a listbox) and
have the rep "assigned" to the event. "Assigning" a rep would create a
record in the EventPerformance table and populate the EventCode,
ContractorID, and DateAssigned fields.

I am working with a FindRecord macro to do the search. I am unsure on how to
complete the remainder of the problem.

Any help would be greatly appreciated.

Thanks,

Jeff

 

[bhicks11 via AccessMonster.com]

Hi Jeff,

I would do this with DLOOKUP(). You can put the code in an EVENT PROCEDURE
that fires after a combobox with your Event, Contractors, etc. are selected.
By the way, you use the EventCode as the bound column in the combobox but set
the column width to zero so that the user sees column two, the event
description. When you are done selecting all the items needed for the event
you can click a button to generate the record.

Bonnie

http://www.dataplus-svc.com

 

[Jeff @ CI]

Thanks for the reply! However, may I please ask for some step-by-step help
on this solution? First, I am required to provide the solution to an Access
2000 user. I do have Access 2007 and am trying to develop in 2000 compatible
format.

In the form, I display the Event information. I then have an unbound
listbox where I want the results of the search returned. How do I send the
results of the search to the listbox? Then how do I click on the listbox
entry to "assign" the rep and have his/her ID and the EventCode put into the
EventPerformance table?

Is the following the way I set up the DLookup()?

DLookup ("[City]", "Cities", )

Jeff

 

[bhicks11 via AccessMonster.com]

Okay, click on the listbox and select changeTo and change it to a combobox.
Right click on the combobox, click on the data tab, Row Source Type should be
Table/Query. Select Row Source, click the ... to the right which will open
the query designer, design your query, close it and you will see the SQL
query in your Row Source. Click on the Event tab, click on the After Update
line, click on ... to design an event procedure - this is where you put your
DLOOKUP().

What you put in the DLOOKUP depends on your environment. Here's an example
and you can look in help for the correct syntax.

Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "([SLNUM] = '" & Me.REFNO & "')
And (

• = '" & Me.List & "')")
  
  You will add a DLOOKUP line for each control on the form you want to populate.
  
  
  Bonnie
  
  http://www.dataplus-svc.com
  
  

  Thanks for the reply! However, may I please ask for some step-by-step help
  on this solution? First, I am required to provide the solution to an Access
  2000 user. I do have Access 2007 and am trying to develop in 2000 compatible
  format.
  
  In the form, I display the Event information. I then have an unbound
  listbox where I want the results of the search returned. How do I send the
  results of the search to the listbox? Then how do I click on the listbox
  entry to "assign" the rep and have his/her ID and the EventCode put into the
  EventPerformance table?
  
  Is the following the way I set up the DLookup()?
  
  DLookup ("[City]", "Cities", )
  
  Jeff
  

  Hi Jeff,

  [quoted text clipped - 34 lines]

 

[Jeff @ CI]

I am a self-taught user of Access. I have done okay with relatively simple
databases for my work. In my current project, I am being tasked with what
they call a simple database. One where it will specifically match up reps
spread across the country (and contracted to recruit for our seminars) with
the cities they have indicated they will recruit in with the seminars we
schedule. I can do the other tasks that I am given for the database, but
cannot get this one aspect to work.

The task is to display an event’s info. Then based on the event’s location,
search for those reps who will work that location and display them in a list.
The user will then “assign†them to work the event. This action will
populate a table to track their performance. I am trying to follow your
instructions, but my lack of know-how has me frustrated with my lack of
understanding how it is done.

To recap the problem and the objects involved, I hope the following helps.

Tables: Fields:
Events
EventID (related to EventPerformance)

EventCity (The field I am trying to search the Cities table for matching
entries)

Cities
ContractorID (Related to Contractors)

Cities (a list of cities they will work)

Contractors
ContractorID (The “key†for the whole database)

FirstName and LastName (I canocate these together for the user)

EventPerformance EventID

ContractorID

DateAssigned (defaulted to use current system date)

Queries:
Event Query restricts
the Events displayed to date() -7 - the source for the Event Query Form

Forms:
Event Query Form The form I am
using to Assign Events to Reps and displays the following:


EventCode

EventLocation (=[EventCity] & “, “ [EventState])

EventDate

EventSpeaker

ContractorID

Unbound Textbox

The user wants to click on a name returned by the search and have them
assigned to the event. The “assignment†is done by putting the EventCode and
ContractorID into the EventPerformance table. I can do the remainder of
the database if I can get past this (ahem) simple task.

Okay, click on the listbox and select changeTo and change it to a combobox.
Right click on the combobox, click on the data tab, Row Source Type should be
Table/Query. Select Row Source, click the ... to the right which will open
the query designer, design your query, close it and you will see the SQL
query in your Row Source. Click on the Event tab, click on the After Update
line, click on ... to design an event procedure - this is where you put your
DLOOKUP().

What you put in the DLOOKUP depends on your environment. Here's an example
and you can look in help for the correct syntax.

Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "([SLNUM] = '" & Me.REFNO & "')
And (

• = '" & Me.List & "')")
  
  You will add a DLOOKUP line for each control on the form you want to populate.
  
  
  Bonnie
  
  http://www.dataplus-svc.com
  
  

  Thanks for the reply! However, may I please ask for some step-by-step help
  on this solution? First, I am required to provide the solution to an Access
  2000 user. I do have Access 2007 and am trying to develop in 2000 compatible
  format.
  
  In the form, I display the Event information. I then have an unbound
  listbox where I want the results of the search returned. How do I send the
  results of the search to the listbox? Then how do I click on the listbox
  entry to "assign" the rep and have his/her ID and the EventCode put into the
  EventPerformance table?
  
  Is the following the way I set up the DLookup()?
  
  DLookup ("[City]", "Cities", )
  
  Jeff
  

  Hi Jeff,

  [quoted text clipped - 34 lines]

  Jeff

 

[Steve Schapel]

Jeff,

You need to have a table that lists the reps, with the cities they are
available for. Assumes any given rep can work to more than one city...
is that right? If so, I can't see where this information is managed in
your database.

 

[Jeff @ CI]

Steve, I do have the table you describe. The table is called Cities - fields
are ContractorID (related to Contractor Table and is key), City and State.

The post in reply to bhicks last response has more detail.

Thanks for your response.