Search info in another form

[summer]

Here is my dilemma:

I have 2 separate forms - Incoming Test Log and Material Test Requirements. The first has new data entered daily. The second is a reference tool. As new materials are received, they are entered into the "Log". The operator then must open the "Requirments" form to see if testing is required on that material. Both forms have the "material number" field and it is the primary key in the "Requirments" form (and of course the underlying table).

This is what I'd like to do:

When a the material number is entered into the "Log", a box will pop up telling him that that material requires or does not require testing, that way he does not have to do a manual search.

In the "Requirements" form, there are check boxes indicating if testing is required or not, as well as all the tests required if applicable.

Can anyone help me write the expression to do this?

I hope what I'm asking makes sense!

THANKS VERY MUCH IN ADVANCE!!!!!

 

[Rob.Mills]

Private Sub MaterialBox_AfterUpdate()

Dim i As Integer

i=Me!MaterialBoxName

if DLookUp("[Name of Yes/No field in table]","[Requirements table Name]","[Requirements table id]=" & i)=Yes Then
MsgBox "This material requires testing.", VbOkOnly
Else
MsgBox "This material does not require testing.", VbOkOnly
End if

 

[summer]

Here's what I did with your suggestion:

Private Sub MATERIAL_NUMBER_AfterUpdate()

Dim i As Integer

i = Me!MaterialNumber

If DLookup("[No Test Required]", "[rawmateriallist]", "[rawmateriallist id]=" & i) = Yes Then
MsgBox "This material requires testing.", vbOKOnly
Else
MsgBox "This material does not require testing.", vbOKOnly
End If


End Sub


I am getting the following error:

Run-time error '2465':

Microsoft Access can't find the field 'MaterialNumber' referred to in your expression.

So I click on Debug and it highlights the i=Me!MaterialNumber line.

If I type it i=Me!Material Number I get Compile error.

Any suggestions???

 

[Rob.Mills]

The best thing you can do to make sure these kind of errors don't happen is to copy the name of the textbox from it's property window and paste it into code. Anytime there's spaces in the name you need to surround it with brackets:

i=Me![Material Number]

That's why I never put spaces in my names. Then I don't need the brackets.

 

[summer]

Thanks for the tip.

Now I'm getting

Run-time error '6':
Overflow

When I click debug, it goes to the same Me! statement.

I clicked on help to understand what that means, but don't understand what I did wrong.

Any ideas? I appreciate your help!

 

[Rob.Mills]

How does the user pick the material number? This is a combobox, yes?

 

[summer]

Actaully, in the "Log" it is not a combo box, but in the "Requirements" it is.

 

[summer]

I tried setting it up as a combo box in both, and I'm getting the same error. Can anyone help?

 

[Rob.Mills]

What you need is a combobox on the form whose control source is the field in the incoming test log table that is the foreign key for the other table.

Then setup for the lookup part of the combobox a select statement that is pulling two columns from the requirements table. The first column should be the requirements table ID, the second should be the name of the requirements.

Now if you want, you can set it so that the user only sees the name in the box but the actual value is the id.

The way you do this is in the properties window set # of columns to 2, bound column is one, column widths=0";1"

Now, the user will just have to remember the name and select that and it will automatically put the correct id in the test log table.

Let me know if this is too confusing.

 

[summer]

Rob,
I set up the combobox as you suggested.

Now my Afterupdate event looks like this:


Private Sub Combo40_AfterUpdate()

Dim i As Integer

i = Me!Combo40

If DLookup("[Test Required]", "[rawmateriallist]", "[Material Number]=" & i) = Yes Then
MsgBox "This material requires testing.", vbOKOnly
Else
MsgBox "This material does not require testing.", vbOKOnly
End If

End Sub

I am still getting the Run-time error '6'. Is there anything else you can suggest for me? I think this is a no-win situation!

 

[summer]

Does anyone know how to clear up this Run-time error?