Access Form design

[crug]

Hi all Its my first time on this forum so please be gentle with me ! I have a list of numbers stored in a field (wieght). I have another field that is the product field. I also have a list of letters (A-D) Now, the weight can be 1kg (through to 20kg) A-D hence four different values in my product field. I need to have a form that the user can choose a letter then enter the weight to show the correct product. I can get a dllookup function to work fine for the fields involved but i need a control to filter the different letter combinations Anybody point me in the right direction

 

[Laurentech]

I'm not sure what exacly it is that you are trying to do, but I'll give it a shot. You have 3 controls on the form: A combo box for weight, a combo box for product ID, and a text box for product description. The user chooses a product ID and a weight, and the description is filled in automatically.

Here's how I would do it:

In the afterupdate event of the first two controls, check to see if the other control is filled in. If not, set the focus to it. If so, get the description.

Am I even close on this one? Let me know and I can give you some code to make it happen.

Larry

 

[crug]

I dont think i explained too well What i want is the user to quickly pick a letter. input a weight (different for each letter) by typing and produce a number via a lookup. The number is unique for each letter/number combination and stored in two fields

 

[Laurentech]

Sorry, I still don't understand completely what it is you need your form to do.

Larry

 

[crug]

Thanks for your tolerance of my ineptitude I`ve attached a pic of the form and table layout The code is

Option Compare Database
Option Explicit
Dim db As Long

Private Sub cboWeight_AfterUpdate()
 Me![GCResult] = DLookup("GC", "TblAll", "[Weight] = '" & cboWeight & "'")
End Sub

Private Sub Form_Current()
    On Error Resume Next
    Dim strCountry As String
    If IsNull(cboWeight.Value) Then
        grpCountry.Value = Null
    End If
' Synchronise country combo with existing city
    strCountry = DLookup("[Letter]", "tblAll", "[City]='" & cboWeight.Value & "'")
    Select Case strCountry
        Case "A"
            grpCountry.Value = 1
        Case "B"
            grpCountry.Value = 2
        Case "C"
            grpCountry.Value = 3
        Case "D"
            grpCountry.Value = 4
    End Select
' Synchronise city combo with existing city
    cboWeight.RowSource = "Select tblAll.Weight " & _
        "FROM tblAll " & _
        "WHERE tblAll.Letter = '" & strCountry & "' " & _
        "ORDER BY tblAll.Weight;"
End Sub

Private Sub Form_Load()
'"DELETE FROM tblData WHERE [Name]=GC"
End Sub

Private Sub grpCountry_AfterUpdate()
    On Error Resume Next
    Dim strCountry As String
    Select Case grpCountry.Value
        Case 1
            strCountry = "A"
        Case 2
            strCountry = "B"
        Case 3
            strCountry = "C"
        Case 4
            strCountry = "D"
    End Select
    cboWeight.RowSource = "Select tblAll.Weight " & _
        "FROM tblAll " & _
        "WHERE tblAll.Letter = '" & strCountry & "' " & _
        "ORDER BY tblAll.Weight;"
End Sub

Im not sure this is the right way to go The table contains A,B,C,D and can be a weight 0.5-20 I want the user to be able to pick a letter, then a weight and a lookup finds the GC number Cheers all