Solved

How do you get combo boxes to sync using lookup.

Posted on 2013-10-23
3
444 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

832 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