[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

How do you get combo boxes to sync using lookup.

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
gigifarrow
Asked:
gigifarrow
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
Nick67Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now