Populating "leads" into a form

[Drewbertus]

Hey all,

Long time lurker, first time poster. I've been working on an access database where a group will be calling into field offices to gather information. When the database opens a form pops up and they can use a combo box to select the agent's number and the information we have on file for them pre-populates in the form. They fill out the rest of the fields and click a submit button which saves to a table on the back end. All of this works fine.

They've now asked me to further automate the form by pre-populating the agent's number from the combo box above from a list of entries looking for agency numbers with the least amount of contacts in the CountOfAgencyName field and with and without a 'Yes' response in the MaxOfClosed field.

So far I've created a query to pull the relevant information but I've been unable to get the form to populate a number as it loads. I've tried several VBA attempts in an 'On Open' event when the form comes up but I don't know if it's possible to achieve. Do you have any suggestions? Thanks!

[June7]

Set the combobox RowSource to pull records from that query and include all 3 fields in the SQL (a multi-column combobox). That will make the related info available for reference by expression in a textbox. Column index begins with 0 so if the Count is in column 2 the index is 1.

=[comboboxname].[Column](1)

[Drewbertus]

Thanks, June!

I altered the AgentBox combo box to pull from the query I'd designed. Unfortunately, the elements that pre-populate the remainder of the known data in the form are derived from an AfterUpdate statement on this box and changing the box now causes those text boxes to return nothing. I was wondering if there were a simple way to have the AgentBox combo box look up and display an Agent # from the query above on opening based on the conditions I stated above. The CountOfAgencyName field is not nearly as important as making sure it excludes anything that has a 'Yes' in the MaxOfClosed field. My coding knowledge is fairly limited but is there any way I could write an OnOpen Event for the form to say something along the lines of AgentBox.Value = DLookup("[Agent #]", "LeadSelection", "[MaxOfClosed]<>'Yes'"). I've tried something similar but i believe it's erroring because there is more than one record that meets this criteria. Is there any way to pull a random number from the pool generated by this constraint?

[ItsMe]

Domain functions are aggregate functions, they see all records that match the expression. However, they do not return an array. Consider the function Dsum(). This domain function will see many records when many records match the criteria in the expression. It will then consider the aggregate and return a Sum.

Although DLookup may see many records, it will only return the value of the first record it finds. If no records match the criteria, it will return Null.

Assigning a value to a combo can be tricky. I do not have all of its rules memorized. The .Value property of the combo is related to the Bound Column. If there is not a Bound Column designated in the Property Sheet, I believe it will display the .Value but does not consider the RowSource.

I am having difficulty understanding what you are trying to automate. The form's On Load Event and On Current event are a couple favorites of mine. Perhaps the On Current event could trigger some code to adjust the RowSource or Value of your combo.

[June7]

Why would making the combobox multi-column cause issue with code? I also am having difficulty understanding what that code is supposed to do. Why do you need code to populate controls? Is this a bound form? Provide code for analysis or even the db. Follow instructions at bottom of my post.

Can restrict the combobox RowSource with filter criteria. The RowSource is just a query.

DLookup should work but domain aggregates can be slow on forms and reports.

[Drewbertus]

Yes, this form is bound to the table it saves into. I've been in discussion with my colleagues about un-binding the form and doing the save function via code to free up any multiple user record locks and allow for a VLookup function to suffice.

[June7]

If this is a multi-user db, are you splitting the db?

Don't really understand what the issue is with record locks. I have split multi-user database and record locking has never been an issue.

DLookup, not VLookup.

[Drewbertus]

It's a multi-user db

it may be a personal preference of the person I asked. My real concern is to get a user ID from the list to pop up automatically in the AgentBox field when the form opens as long as the Closed field in the table it feeds into is not 'Yes'.

[June7]

Is the form opened to existing record or new record?

Is the combobox bound?

Don't really understand what you mean by 'pop up automatically in the AgentBox'. The combobox RowSource will show whatever records its SQL is set for. If bound, the combobox value will be whatever is in the field.

Is this a split database?

[Drewbertus]

The issue I'm encountering with the multi-column combo box may be due to how it's coded. The combobox [AgentBox] has an AfterUpdate event with the following code:

Private Sub AgentBox_AfterUpdate()
AgencyBox.Value = DLookup("[Agency Name]", "Enrollment Table", "ID=" & [AgentBox])
AgencyPhoneBox.Value = DLookup("[Agency phone number that will be forwarded:]", "Enrollment Table", "ID=" & [AgentBox])
CrossSellBox.Value = DLookup("[Cross-Sell Start Date]", "Enrollment Table", "ID=" & [AgentBox])
CForwardBox.Value = DLookup("[Call Forwarding Start Date]", "Enrollment Table", "ID=" & [AgentBox])
CancellationBox.Value = DLookup("[Cancellation Start Date]", "Enrollment Table", "ID=" & [AgentBox])
CFAlertBox.Value = DLookup("[Call Forwarding Non-Usage]", "Enrollment Table", "ID=" & [AgentBox])
PopBox.Value = DLookup("[Screen Pop Errors]", "Enrollment Table", "ID=" & [AgentBox])
CallsBox.Value = DLookup("[CountOfAgencyName]", "InteractionsQuery", "[AgencyName]=Forms!DataEntry!AgencyBox")

This is what is populating the agency information fields after an agent number is selected from the drop down. When I switched to a multi-column box, the fields began not returning data.

[June7]

The criteria in 7 of those DLookup expressions uses ID field. Is ID field included in the combobox RowSource? Or is the value of combobox the Agent#?

The 8th expression uses AgencyName in criteria but AgencyName is not in the query as posted. Also, the reference to AgencyBox is within quotes and should be concatenated.

If all of those fields could be included as columns in the combobox by joining tables/queries, the VBA and DLookup would not be needed.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Drewbertus]

Looking back, the ID field is part of the combobox rowsource.

The 8th expression uses AgencyName which is the first field of a query which looks at the table of completed forms and counts how many times the agency's name has appeared. It has two fields AgencyName and CountOfAgencyName.

I can look into joining these, I just haven't done so in the past and my relative inexperience coding has just kept me sticking with what I know. Time to expand my learning

Agreed on the special characters in naming conventions. This was a temp file they gave me to work with. I was planning to correct it once I was sure everything worked.

[ItsMe]

Also, all of your Where criteria is using the bound column of the combo. Might consider explicitly defining the column you want to compare.

Instead of
"ID=" & [AgentBox])

something like
..."ID=" & Me.AgentBox.Column(0)) ' for the first column
or
..."ID=" & Me.AgentBox.Column(1)) ' for the second column
and
..."[AgencyName]='" & Me.AgentBox.Column(0)) & "'" ' for literal text

using the fully qualified name should work for text but I do not believe you can get the column property using the fully qualified name. You will need VBA.

[June7]

ItsMe, not sure what your last statement means. A textbox can reference any column of a combobox.

[ItsMe]

I was referring to
..."[AgencyName]=Forms!DataEntry!AgencyBox")

I did not test it but it looks like that would work in SQL but not VBA. I have not tried real hard but am not aware of a way to reference combobox column in SQL.