By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

form controls appear disconnected but form built on query

imrosie
100+
P: 222
I have a form built on a query that allows for searching of customer names thru a combobox control that's UNBOUND; it works off of a row source table query-- SELECT [CustomerID], [FirstName] & ", " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];, to obtain the names.

Once you select a name from the list, the name shows up in the control, but other information associated with the name doesn't populate into the other text field controls on the form (such as address, city, state, phone, zip, etc.)....

I need to be able to search(and add customers to the database) because I have a NotInList event as part of this control, with limit to list equal to No.

help,,,,,thanks..

Rosie
Jun 6 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,633
I have a form built on a query that allows for searching of customer names thru a combobox control that's UNBOUND; it works off of a row source table query-- SELECT [CustomerID], [FirstName] & ", " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];, to obtain the names.

Once you select a name from the list, the name shows up in the control, but other information associated with the name doesn't populate into the other text field controls on the form (such as address, city, state, phone, zip, etc.)....

I need to be able to search(and add customers to the database) because I have a NotInList event as part of this control, with limit to list equal to No.

help,,,,,thanks..

Rosie
What is the Record Source for the Form itself and which Fields does it consist of?
Jun 6 '07 #2

imrosie
100+
P: 222
What is the Record Source for the Form itself and which Fields does it consist of?
The record source of form is a query called 'search customer' (contains almost all of the fields in the customer table and the order tables (their inner joined on
the customerid. here's the code:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.FirstName, Customers.LastName, [LastName] & ", " & [FirstName] AS FullName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ContactTitle, Customers.PhoneNumber, Customers.CellNumber, Customers.FaxNumber, Customers.Email, Customers.ShipName, Customers.ShipAddress, Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipCountry, Customers.ShipZIPCode, Customers.ShipPhoneNumber, Customers.ShipFaxNumber, Customers.Notes, Orders.CustomerID, Customers.ZIPCode
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY [FirstName] & ", " & [LastName];


The row source query (to look up a customer) on form works great,,,thanks to lots of help from the scripts,,,
here's that code:
SELECT [CustomerID], [FirstName] & ", " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];

I'm at a loss here. thanks for any help you can provide.
Rosie
Jun 6 '07 #3

Expert 100+
P: 218
The row source query (to look up a customer) on form works great,,,thanks to lots of help from the scripts,,,
here's that code:
SELECT [CustomerID], [FirstName] & ", " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];
Rosie
Looks to me as if you need to set your form filter to the CustomerID column of your dropdown list. You might also think about using the dropdown's AfterUpdate event to achieve this, eg.
Expand|Select|Wrap|Line Numbers
  1. Me.FilterOn = True
  2. Me.Filter = "CustomerID = " & Me.CustomerCombo
  3. Me.Requery
You will have to set the combo box Bound Column value to 1, so that it returns the CustomerID.

HTH
Steve
Jun 6 '07 #4

ADezii
Expert 5K+
P: 8,633
The record source of form is a query called 'search customer' (contains almost all of the fields in the customer table and the order tables (their inner joined on
the customerid. here's the code:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.FirstName, Customers.LastName, [LastName] & ", " & [FirstName] AS FullName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ContactTitle, Customers.PhoneNumber, Customers.CellNumber, Customers.FaxNumber, Customers.Email, Customers.ShipName, Customers.ShipAddress, Customers.ShipCity, Customers.ShipStateOrProvince, Customers.ShipCountry, Customers.ShipZIPCode, Customers.ShipPhoneNumber, Customers.ShipFaxNumber, Customers.Notes, Orders.CustomerID, Customers.ZIPCode
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY [FirstName] & ", " & [LastName];


The row source query (to look up a customer) on form works great,,,thanks to lots of help from the scripts,,,
here's that code:
SELECT [CustomerID], [FirstName] & ", " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];

I'm at a loss here. thanks for any help you can provide.
Rosie
Assuming the Bound Column for the Combo Box is [CustomerID] and your Combo Box Name is cboFindPerson, then try this code in the AfterUpdate() Event of your Combo Box:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboFindPerson_AfterUpdate()
  2. 'To show a Unique Record based on [CustomerID]
  3. Dim intSearchID As Integer
  4.  
  5. If IsNull(Me![cboFindPerson]) Then Exit Sub
  6.  
  7. intSearchID = Me![cboFindPerson]
  8.  
  9. Me.Filter = "[CustomerID] =" & SearchID
  10. Me.FilterOn=True
  11. End Sub
NOTE: Remember to turn FilterOn = False at some point.

Alternative 2 does not Filter the Form but uses the FindFirst Method:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboFindPerson_AfterUpdate()
  2. Dim rst As DAO.Recordset
  3.  
  4. Set rst = Me.RecordsetClone
  5.  
  6. rst.FindFirst "[CustomerID]=" & Me![cboFindPerson]
  7.  
  8. If Not rst.NoMatch Then
  9.   Me.Bookmark = rst.Bookmark
  10. Else
  11.   'Not found!
  12. End If
  13. End Sub
Jun 6 '07 #5

imrosie
100+
P: 222
Assuming the Bound Column for the Combo Box is [CustomerID] and your Combo Box Name is cboFindPerson, then try this code in the AfterUpdate() Event of your Combo Box:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboFindPerson_AfterUpdate()
  2. 'To show a Unique Record based on [CustomerID]
  3. Dim intSearchID As Integer
  4.  
  5. If IsNull(Me![cboFindPerson]) Then Exit Sub
  6.  
  7. intSearchID = Me![cboFindPerson]
  8.  
  9. Me.Filter = "[CustomerID] =" & SearchID
  10. Me.FilterOn=True
  11. End Sub
NOTE: Remember to turn FilterOn = False at some point.

Alternative 2 does not Filter the Form but uses the FindFirst Method:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboFindPerson_AfterUpdate()
  2. Dim rst As DAO.Recordset
  3.  
  4. Set rst = Me.RecordsetClone
  5.  
  6. rst.FindFirst "[CustomerID]=" & Me![cboFindPerson]
  7.  
  8. If Not rst.NoMatch Then
  9.   Me.Bookmark = rst.Bookmark
  10. Else
  11.   'Not found!
  12. End If
  13. End Sub

Hello, I"m trying your first alternative, then you posted number 2, is there an advantage of number 2 over number 1? thanks
Jun 6 '07 #6

ADezii
Expert 5K+
P: 8,633
Hello, I"m trying your first alternative, then you posted number 2, is there an advantage of number 2 over number 1? thanks
  1. Alternative 1 - Applies a Filter to the Form and only 1 Record is visible (the Filtered one). You would then have to remove the Filter if you wanted to view all Records again.
  2. Alternative 2 - All Records in the underlying RecordSource for the Form are displayed. It just makes the Record that you are searching for the Current Record. If you don't have a specific reason for displaying the Filtered Record only, I would think the second Option would be more suitable.
Jun 7 '07 #7

imrosie
100+
P: 222
  1. Alternative 1 - Applies a Filter to the Form and only 1 Record is visible (the Filtered one). You would then have to remove the Filter if you wanted to view all Records again.
  2. Alternative 2 - All Records in the underlying RecordSource for the Form are displayed. It just makes the Record that you are searching for the Current Record. If you don't have a specific reason for displaying the Filtered Record only, I would think the second Option would be more suitable.
Thanks so much ADezii,

I had implemented the 1st alternative and it was working just fine. I didn't like that I could only have the one filtered record available. I have no specific reason to do that. So I'm going to switch to alternative 2. Really appreciated. thanks.
Jun 7 '07 #8

ADezii
Expert 5K+
P: 8,633
Thanks so much ADezii,

I had implemented the 1st alternative and it was working just fine. I didn't like that I could only have the one filtered record available. I have no specific reason to do that. So I'm going to switch to alternative 2. Really appreciated. thanks.
No problem. It you have any other questions, feel free to ask.
Jun 7 '07 #9

imrosie
100+
P: 222
No problem. It you have any other questions, feel free to ask.
Hello ADezii,

I do have a problem that is when you add a new name to the list (NotInList event), customer information from the previous customer doesn't clear out, it sits there. Then when I manually erase it and begin adding info on the new customer, it doesn't store. Do you know what might be broken?

Here is my NotInList code:

Private Sub fullcustname_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String 'for capturing the parsed components of FullName
'the FullName entered is not the table list; prompt user to ok adding it to list
intAnswer = MsgBox("" & Chr(34) & NewData & _
Chr(34) & " isnīt on the list." & vbCrLf & _
"Would you like to add it?" _
, vbQuestion + vbYesNo, "Express")

' Background process for Parsing FullName entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then 'FullName entered----> First Last
strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then 'FullName entered----> Last, First
strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
MsgBox "You've entered the name without a comma between the first and last name."
Exit Sub
End If
'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
"VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
If intAnswer = vbYes Then
fullcustname.Undo

MsgBox "The name has been added to the list." _
, vbInformation, "Express"
'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
Response = acDataErrAdded
Else
MsgBox "Please select a name on the list." _
, vbInformation, "Express"
Response = acDataErrContinue
End If
End Sub

thanks so much
Jun 7 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.