If-Then statements when referenceing a subform? Please help ;(

[Benjamin Bolduc]

Hi everyone!
I'm having trouble with form I'm working on.
I have an Option Group on the main form that has 3 options and a subform where I put inventory records. I want the form to fill in a feild on the subform automatically depending on which option is selected as I fill in records. I thought I did for a minute, but it will only fill in the first record of the subform. Here is my code:


Private Sub Form_Current()
If Me.CompanyChoose = 1 Then
Me.Glass_Inventory_Entry_subform!Company = "Lyndonville (Nakano)"

End If
If Me.CompanyChoose = 2 Then
Me.Glass_Inventory_Entry_subform !Company = "Crossville (Nakano)"

End If
If Me.CompanyChoose = 3 Then
Me.Glass_Inventory_Entry_subform !Company = "Oasis"

End If
End Sub

Does anyone know why it only fills in the first record? And how to fix it? Any help would be greatly appreciated since I already told my boss that this was done and I have to present it later this afternoon ;(

Thanks!

 

[Pat Hartman]

The Current event is not fired until you move the cursor into the record.

To solve the problem, add a calculated field to the query.

Select Choose(CompanyChoose,"Lyndonville (Nakano)", "Crossville (Nakano)","Oasis") As Company, ....
From ...;

 

[Benjamin Bolduc]

Thanks for responding.
I'm not sure what's going on. Even when the the focus goes to the new record, it still leaves only the first record with the Company Field filled in. I filled in 5 records just to be sure and only that first one worked.
If adding a calculated field will fix this, I'll need to know exactly what to put in the query. (I have no experience with SQL)

Thank you so much for your help, I'm desperate here. It's amazing how Access has the ability to cause so many headaches.

 

[Benjamin Bolduc]

Anyone have any ideas? The deadline for this project is tommorow at noon. This is the only thing left I have to do.
I'm just getting a little anxious

Thanks!

 

[doulostheou]

If you want this to work as you are filling in the records, it seems it would make more sense to write the code into your subform.

That way it will fill in the company you have selected on the MainForm each time you move to a new record. If I'm not understanding what you are trying to accomplish and you need it to run the other way, the only thing I could think to do would be to write a loop that would keep setting the subform equal to the company while going to the next record until it reached the end. This would involve figuring out how many records were in the recordset, etc. I'm not very experienced in dealing with recordsets.

As a side note, this is not crucial to your code working properly, it would be more effective to use If, ElseIf when dealing with the option group:

If Option1 Then
DoSomething
ElseIf Option2 Then
DoSomething
End If

It just saves a little space.

[This message has been edited by doulostheou (edited 04-15-2002).]

 

[Benjamin Bolduc]

I guess it would make more sense to do it from the subform, I'm just having having syntax problems with referencing the "Company" Field on the subform from the "ChooseCompany" Option group on the Main Form.
I've searched the archives and found some examples but I just cant make my code work.

Once again, I appreciate all your help

 

[doulostheou]

I don't currently have time to test this, but I believe the syntax for writing the code from the subform would simply be to add the following code into the Current Event of the subform:

If Forms!YourMainFormName!CompanyChoose = 1 Then
Company = "Lyndonville (Nakano)"
ElseIf Forms! etc.

 

[Benjamin Bolduc]

Woohoo! That worked! I had to make a few adjustments but now my form is working exactly how I wanted it to. Thank you all for your help. I don't know what I'd do without this forum