VBA code for bringing field data

[acnovice]

I have a form with a subform (datasheet view). In the subform, there are BallsIn, AM's Job, PM's Job, and Customer fields.

I gave Open Issue field instead of 3 fields (AM's Job, PM's Job, and Customer).

So, if BallsIn is "AM", it corresponds to "AM's Job".

BallsIn Open Issue(AM's Job, PM's Job, Customer)
------- -------------
AM AM's Job
PM PM's Job
Customer Customer
... ...

I want to write VBA code for this.
Any help will be appreciated.

 

[flyover789]

could you please be more specific:
what is the recordsource of your main form
what is the recordsource of your sub form



[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[acnovice]

Ok...
The recordsource of main form -- tblInquiryLog
The recordsource of sub form -- qryCISQNo -- tblInquiryLog

Thank you.

 

[acnovice]

I'm thinking about it like below.

If BallsIn is "AM" Then
bring "AMsJob" to Open Issue
ElseIf BallsIn is "PM" Then
bring "PMsJob" to Open Issue
Else
bring "Customer" to Open issue
End If

Can I do this using SQL statement ?
It feels like more simple.

** All of the fields is just text box.

 

[flyover789]

yes, most probably you can. but you have to give us more information about your tables and queries (fields, joins ...)


[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[acnovice]

Main form : frmCISQNo
Sub form : sfrmCISQNo
Query : qryCISQNo

The recordsource in main form, sub form and query is using same table "tblInquirylog"

tblInquirylog(fields);
QNo
BallsIn(AM, PM, Customer)
AMsJob
PMsJob
Customer
...

qryCISQNo(fields);
QNo
BallsIn(AM, PM, Customer)
AMsJob
PMsJob
Customer
Open issue
...

sfrmCISQNo(fields);
QNo
BallsIn
Open issue
...
Fields in sub form types are text box.

Earlier, we saved data;
If BallsIn is AM, saved description in AMsJob field.
If BallsIn is PM, saved description in PMsJob field.
If BallsIn is Customer, saved description in Customer field.

From sub form, I want to put Open Issue fields for three description.
The format of subform will be following.

Q No BallsIn Open Issue
----- ------- -------------
1000 AM display data from "AMsJob"
1001 PM display data from "PMsJob"
1002 Customer display data from "Customer"

Thank you.

 

[flyover789]

looks like a normalization issue:
is it correct, that you have only three possible values in field BallsIn?
If yes, why store it in separate columns additionally (and redundantly)?

I think you can just link your subform to the mainform via an inner join of the BallsIn Field of both recordsources.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[acnovice]

Yes, you're right... it looks like normalization issue.

There are some redundant fields.
I will think of it how to solve this problem.

Thank you.