return records that apply

[steveh]

I have a table that I can enter a serial number and I get the service records for that serial number. I have set it up where the field ServiceDate on the form is automatically filled in based on the serial number I input. The problem is there are multiple ServiceDate entries and I want to have a drop down that will list the ServiceDate associated with that serial number only. I am using the following sub

Private Sub SerialNumber_AfterUpdate(
Me.ServiceDate = DLookup("ServiceDate", "TblServiceRecords"
Me.ProblemDescription = DLookup("problemDescription", "TblServiceRecords"
Me.Technician = DLookup("Technician", "TblServiceRecords"
Me.PartsReplaced = DLookup("PartsReplaced", "TblServiceRecords"
End Su

Problem is it returns all service dates for all serial numbers. What am I missing?

 

[John Vinson]

I have a table that I can enter a serial number and I get the service records for that serial number. I have set it up where the field ServiceDate on the form is automatically filled in based on the serial number I input. The problem is there are multiple ServiceDate entries and I want to have a drop down that will list the ServiceDate associated with that serial number only. I am using the following sub;

Private Sub SerialNumber_AfterUpdate()
Me.ServiceDate = DLookup("ServiceDate", "TblServiceRecords")
Me.ProblemDescription = DLookup("problemDescription", "TblServiceRecords")
Me.Technician = DLookup("Technician", "TblServiceRecords")
Me.PartsReplaced = DLookup("PartsReplaced", "TblServiceRecords")
End Sub

Problem is it returns all service dates for all serial numbers. What am I missing?

DLookUp isn't really the appropriate tool here. You're getting exactly
what you ask for - DLookUp("ServiceDate", "TblServiceRecords") tells
Access "Open the table TblServiceRecords and give me the first service
date it finds".

I think what you want to do instead is use the SerialNumber control to
*find the record for that serial number* and display the entire record
on the Form. For the user's convenience I'd suggest using a Combo Box
instead of a textbox - this will let the user pick valid serial
numbers from a list, rather than having to type it in exactly, and the
Autocomplete feature of a combo will let them select a record by just
typing a few characters.

Be sure it's an UNBOUND combo, or you'll overwrite the current
record's serial number! Use code like this (which will work with a
textbox too, if you prefer):

Private Sub cboFindSerialNumber_AfterUpdate()
Dim rs AS DAO.Recordset
Set rs = Me.Recordsetclone ' get the Form's record set
rs.FindFirst "[SerialNumber] = '" & Me.cboFindSerialNumber & "'"
If rs.NoMatch Then
MsgBox "This number not in table, try again"
Else
Me.Bookmark = rs.Bookmark ' synch the form to the found record
End If
End Sub

 

[steveh]

Thanks John! I appreciate your response. I am not sure I understand completely(forgive my newbieness). I have something similar to what you suggest on the SerialNumber field although not nearly as eloquent.

Private Sub SerialNumber_NotInList(NewData As String, Response As Integer
Dim ctl As Contro

' Return Control object that points to combo box
Set ctl = SerialNumbe

' Prompt user to verify they wish to add new value
If MsgBox("serial number is not in list. Do you want to add a new one?", vbOKCancel) = vbOK The
Response = acDataErrContinu
' Open the Products Form

DoCmd.OpenForm "FrmProducts", acNormal, , , acFormAdd, acWindowNorma

Els
'If user chooses Cancel, suppress error messag
'and undo changes
ctl.Und

End I


End Su

I will look at that again using your suggestion.

The problem I'm having is with the records associated with the serial number. One of the records is a service date. The problem is that a serial number can have several service dates associated with it. I would like the combo for the Service date to allow the user to select the service date they are interested in and the information from that service date to populate the fields on my form

Hope I'm not just being thick hea

Thanks again

----- John Vinson wrote: ----

On Mon, 9 Feb 2004 21:26:06 -0800, steveh <[email protected]
wrote

I have a table that I can enter a serial number and I get the service records for that serial number. I have set it up where the field ServiceDate on the form is automatically filled in based on the serial number I input. The problem is there are multiple ServiceDate entries and I want to have a drop down that will list the ServiceDate associated with that serial number only. I am using the following sub
Me.ServiceDate = DLookup("ServiceDate", "TblServiceRecords"
Me.ProblemDescription = DLookup("problemDescription", "TblServiceRecords"
Me.Technician = DLookup("Technician", "TblServiceRecords"
Me.PartsReplaced = DLookup("PartsReplaced", "TblServiceRecords"
End Su

DLookUp isn't really the appropriate tool here. You're getting exactl
what you ask for - DLookUp("ServiceDate", "TblServiceRecords") tell
Access "Open the table TblServiceRecords and give me the first servic
date it finds".

I think what you want to do instead is use the SerialNumber control t
*find the record for that serial number* and display the entire recor
on the Form. For the user's convenience I'd suggest using a Combo Bo
instead of a textbox - this will let the user pick valid seria
numbers from a list, rather than having to type it in exactly, and th
Autocomplete feature of a combo will let them select a record by jus
typing a few characters

Be sure it's an UNBOUND combo, or you'll overwrite the curren
record's serial number! Use code like this (which will work with
textbox too, if you prefer)

Private Sub cboFindSerialNumber_AfterUpdate(
Dim rs AS DAO.Recordse
Set rs = Me.Recordsetclone ' get the Form's record se
rs.FindFirst "[SerialNumber] = '" & Me.cboFindSerialNumber & "'
If rs.NoMatch The
MsgBox "This number not in table, try again
Els
Me.Bookmark = rs.Bookmark ' synch the form to the found recor
End I
End Su

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

 

[John Vinson]

The problem I'm having is with the records associated with the serial number. One of the records is a service date. The problem is that a serial number can have several service dates associated with it. I would like the combo for the Service date to allow the user to select the service date they are interested in and the information from that service date to populate the fields on my form.

Where are these service dates stored? You should have at least two
tables - Parts (with Serial Number as the primary key) and Service,
related one-to-many to this table. Are you trying to update a Parts
record or a Service record? Might you not need two combo boxes, one
dependent on the other?

 

[steveh]

I have the following table

TblProduct
SerialNumber(PK
Mak
Mode
Categor

TblServiceRecord
ServiceDat
Descriptio
PartsReplace
DateComplete

One-to-many relationship

I am trying to update service records of the products serial number. There are multiple service dates per serial number

Thanks John

----- John Vinson wrote: ----

On Tue, 10 Feb 2004 16:51:05 -0800, steveh <[email protected]
wrote

The problem I'm having is with the records associated with the serial number. One of the records is a service date. The problem is that a serial number can have several service dates associated with it. I would like the combo for the Service date to allow the user to select the service date they are interested in and the information from that service date to populate the fields on my form

Where are these service dates stored? You should have at least tw
tables - Parts (with Serial Number as the primary key) and Service
related one-to-many to this table. Are you trying to update a Part
record or a Service record? Might you not need two combo boxes, on
dependent on the other

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

 

[John Vinson]

I am trying to update service records of the products serial number. There are multiple service dates per serial number.

but... the first part of the thread had to do with a combo box not in
list event...!?

I'd just create a Form based on the Products table, and a Subform
based on the service table. To add a new service record just add it on
the subform.

 

[steveh]

Thanks John! This is my first attempt at a db. It is apparent I am in over my head. I think I need to step back and look at my db design. Not quite sure how to evaluate if I am trying to accomplish the right thing. I really appreciate your help.