populate field based on if statement 3

[Krykota]

Greetings.
I have a form for data entry 'frmWOEntry' where the user selects from a list box "A" (has 3 columns, ID, Name, Brand) or from a seperate list box "B" (again 3 columns; ID, Name, Brand) as the item of focus for the entry form. The two list boxes contain two different catagories and cannot be combined.

I would like to create a 'if then' statement in vba for a control's OnExit event based on the user's selection.

The logic is that if the list box A has an item selected then the Brand and Name would be written into a control [WOName] and saved. The same action would be done if an item in list box B is selected.

My attempt looks like this:

Private Sub WOName_Exit(Cancel As Integer)

If [A] Is NOTNull Then
[WOName]=[AName] + " " + [ABrand]

Else
[WOName]=[BName] + " " + [BBrand]

END if

End Sub

I am getting a compile error.

Any help is HUGELY appreciated.

 

[Prattaratt]

I would execute an OnChange event for each list box control that would execute the change on the WOName control. This way, whenever an item in one list box is selected, the changes will overwrite any previous selection.

 

[dhookom]

I would use the After Update event of each list box to set the value of the WOName.

Duane
Hook'D on Access
MS Access MVP

 

[Krykota]

Thank you for the response, however I have a couple of issues.
1- I don't see an OnChange event for the list box control. But I think that After Update will work in its place.

2- Using After Update or even On Click events, I am unable to pull the data from either the table or the column with the data in them. The form is a pop up form not a subform. Would you please provide an example of what you are talking about?

Thanks!

 

[TheAceMan1]

How are ya Krykota . . .

I have code prepared to give ya, but as usual control names are never given. So whats the actual names of the Listboxes?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Krykota]

list box a is List17 and b is list 37. List 17 goes to a control source of CID. List 37 feeds NWID.

On list 17, columns are id, brand, and name from a table called CID

same columns on list 37 but from table NWID.

As Prattaratt pointed out, it is important that the user can only select one item from either list, not both. While I am allowing the user to change or correct a wrong item, the final choice is what I need in the control (to be saved) named WOName.

Thanks for your help.

 

[Prattaratt]

I am now unclear what you are trying to accomplish. Are you:
1. Trying to modify the contents of a control on the same form as the list boxes?

2. Wanting to open a 2d form based on selected data from the 1st?

3. Wanting to modify data on a form that is already open based upon the values of a pop-up form?

I was operating under the impression of the 1st option. Sample code for that follows:

'Assumes that the Name column is the 2nd column in the listbox and that Brand is the 3rd

Private Sub A_AfterUpdate()
  WOName.Text=A.Column[2].Value + " " + A.Column[3].Value
End Sub

Private Sub B_AfterUpdate()
  WOName.Text=B.Column[2].Value + " " + B.Column[3].Value
End Sub

For the 2nd option, What I would try would be to lock the other control in the afterupdate event, then when teh form closes, open the entry form with a docmd statement that sets the open args to the string you want the control to display. Then Code the main form's open event to read from teh open args and set the control appropiately.

'---------------------Popup module------------------
Private Sub A_AfterUpdate()
  B.Locked = True
End Sub

Private Sub A_GotFocus()
  If A.Locked Then A.Locked = False
End Sub

Private Sub B_AfterUpdate()
  A.Locked = True  
End Sub

Private Sub B_GotFocus()
  If B.Locked Then B.Locked = False
End Sub

Private Sub Form_Close()
Dim S As String
  If A.Locked Then
    S = B.Column(2).Value + " " + B.Column(3).Value
  Else
    S = A.Column(2).Value + " " + A.Column(3).Value
  End If
  DoCmd.OpenForm FormName:="frmWOEntry", OpenArgs:=S
End Sub

'--------------frmWOEntry Module ------------------
private Sub Open()
Dim S as String
  S = OpenArgs
  WOName.Text = S
End Sub

For the 3rd option, I would try this:

Private Sub A_AfterUpdate()
  Forms("frmWOEntry").Controls("WOName").Text=A.Column[2].Value + " " + A.Column[3].Value
End Sub

Private Sub B_AfterUpdate()
  Forms("frmWOEntry").Controls("WOName").Text=B.Column[2].Value + " " + B.Column[3].Value
End Sub

 

[TheAceMan1]

Roger That Krykota . . .

In the [blue]After Update[/blue] event of [blue]List17[/blue], copy/paste the following:

[blue]   Me!WOName = Me!List17.Column(2) & " " & Me!List17.Column(1)
   Me!List37.RowSource = Me!List37.RowSource [green]'requery for no selections![/green][/blue]

In the [blue]After Update[/blue] event of [blue]List37[/blue], copy/paste the following:

[blue]   Me!WOName = Me!List37.Column(2) & " " & Me!List37.Column(1)
   Me!List17.RowSource = Me!List17.RowSource [green]'requery for no selections![/green][/blue]

Note: be sure to [blue]rem[/blue] out or delete other code you have to deal with this. This is just to prevent interaction.

When you make a selection in either listbox, [blue]WOName[/blue] is updated while the alternate listbox is [blue]requeried[/blue] for no selections. The user simply goes back & forth until satisified!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Krykota]

Prattaratt,

Thank you. The first option was what I was after. I appreciate your time and effort in helping me.
Quick, easy and did the trick.

TheAceMan1,

I liked your solution for its pointed limitations, something Prattaratt didn't have privy to when he provided his solution.

To both, thank you so much for your help. I will be testing these with my development team (gotta have the kids (l)earn their keep).

K

 

[Prattaratt]

Point to Note: There is an indexing error in my solution(s). I was working with one based indexing for the Column property, but it is one of the properties that is zero based. So shift the indexes to 1 & 2 instead of 2 & 3. It gets me all the time, and it was getting too late for me when I wrote it last night and I missed the error.

 

[Krykota]

Yes I had noticed that when I was typing in the solution. I hate the simple copy and paste. I don't learn anything when I copy and paste.

You are still cool with me Prattaratt!