Solved

How do you get combo boxes to sync using lookup.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now