Solved

How do you get combo boxes to sync using lookup.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 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

21 Experts available now in Live!

Get 1:1 Help Now