Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How do you get combo boxes to sync using lookup.

Posted on 2013-10-23
3
449 Views
Last Modified: 2013-10-25
I need the user to be able to do a search for a record based on the Serial number and the Date and form id.
It searches on serial number and will bring up the data for that serial number.

I  made a lookup for the date but it will only lookup the form Id that matches with the date. It will not bringup the serial number that relates to the date .


I have this in a unbound combo text box and it works fine when searching for a serial number.
Private Sub SerialNumber_AfterUpdate()
If Me.Dirty Then Me.Dirty = False ''(n.b. often the preferred method)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 ''(n.b. deprecated)
DoCmd.RunCommand acCmdSaveRecord
Forms!frmFS3QuestionairesHistory!TimeInduction = DLookup("[TimeInduction]", "[tblFS3Responses]", "[Serial]= Forms![frmFS3QuestionairesHistory]![Serial]")
Forms!frmFS3QuestionairesHistory!Model = DLookup("[Model]", "[tblFS3Responses]", "[Serial]= Forms![frmFS3QuestionairesHistory]![Serial]")
Forms!frmFS3QuestionairesHistory!NameofInductor = DLookup("[NameofInductor]", "[tblFS3Responses]", "[Serial]= Forms![frmFS3QuestionairesHistory]![Serial]")
Forms!frmFS3QuestionairesHistory!FormID = DLookup("[FormID]", "[tblFS3Responses]", "[Serial]= Forms![frmFS3QuestionairesHistory]![Serial]")
DoCmd.SearchForRecord , "", acFirst, "[Serial] = " & "'" & Screen.ActiveControl & "'"
example
I have this for my date field it will conect the form id based on the date But doesnt connect data for the serial number also.

Forms!frmFS3QuestionairesHistory!Serial = DLookup("[Serial]", "[tblFS3Responses]", "[TimeInduction]= Forms![frmFS3QuestionairesHistory]![TimeInduction]")
Forms!frmFS3QuestionairesHistory!FormID = DLookup("[FormID]", "[tblFS3Responses]", "[TimeInduction]= Forms![frmFS3QuestionairesHistory]![TimeInduction]")
DoCmd.SearchForRecord , "", acFirst, "[TimeInduction] = " & "'" & Screen.ActiveControl & "'"
How do I have the user be able to search the records based on the date that will popup serial numbers just for that date?
What do I need to do for the FormID, Date, Serial number to be in sync to sort . for the data for the record?
0
Comment
Question by:gigifarrow
3 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 39596905
Not quite sure I understand what your doing.  If you can add a bit more detail, it would be helpful.

Aside from that, two comments:

1. If this is in code, then your Dlookup()'s are incorrect.  This:

DLookup("[Serial]", "[tblFS3Responses]", "[TimeInduction]= Forms![frmFS3QuestionairesHistory]![TimeInduction]")

Should be:

DLookup("[Serial]", "[tblFS3Responses]", "[TimeInduction]= " & Forms![frmFS3QuestionairesHistory]![TimeInduction])

Right now, you literally have it looking for a TimeInduction value of:

"Forms![frmFS3QuestionairesHistory]![TimeInduction]"

2. All the Dlookups are a performance drain.  If you need to use more then 2 or 3 for the same record, your better off to open a recordset with the record in question, then have access to all the fields.

Jim.
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 167 total points
ID: 39597317
I also don't understand what you are trying to do.

I agree with Jim's comments about the Dlookups.

Here is a search for example that may help:

http://www.allenbrowne.com/ser-62.html
A flexible search form provides several boxes where a user can enter criteria. An efficient search form creates the criteria from only those boxes where the user enters a value.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 166 total points
ID: 39598530
I have to agree with the other posters that it is not quite clear what you are doing, or upon what form you are trying to do it.  More detail would be appreciated.

DoCmd.SearchForRecord is new to me -- as I still develop in A2003.
That is where your problem is, though
DoCmd.SearchForRecord , "", acFirst, "[Serial] = " & "'" & Screen.ActiveControl & "'" and
DoCmd.SearchForRecord , "", acFirst, "[TimeInduction] = " & "'" & Screen.ActiveControl & "'"

You're only telling it to search by [Serial] or by [TimeInduction] when you want it to find BOTH and FormID to boot

Probably removing these AfterUpdate events, and adding a button that says "Search!" with code like this behind it:
Dim SearchString as String
SearchString = "[Serial] = " & chr(34) & Me.Serial.Value & Chr(34) 
SearchString = SearchString & " AND [TimeInduction] = " & Chr(34) Me.TimeInduction.Value
DoCmd.SearchForRecord , "", acFirst, SearchString

Open in new window

might give you what you want...but


All of your DLookup code (Forms!frmFS3QuestionairesHistory!Whatever) is changing Values on the form.  Are all of those controls unbound controls (not connected to a datasource)?  Because I don't think you are intending to change data, are you?

An unbound lookup form is a handy thing -- but it should be opening another form to the desired record -- and this code does not appear to do that.

More detail please!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question