Record select on sub form

[Rince]

Hi,
Im new to access and vb and with help from this forum I have managed to put together a database. I have attached my effort.

In the header of frmQuoteLedger I have txtboxes where I enter new information.
In details of frmQuoteLedger I have a subform containing a Listbox which displays all of the records.

I would like to be able to navigate the header records by selecting a record in the listbox. At the moment there seem to be no link between the main and sub form. So when I click a line in the listbox I would like the relevent info to be displayed in the header and then I can use my Command buttons to perform whatever task I want.

Thanks in advance for any help,

Cheers,

Sean

 

[CyberLynx]

At the moment there seem to be no link between the main and sub form

You basically hit the nail on the head with that comment. Things would have been so much easier if your SubForm Form (frmMain) was a Continuous Form or Datasheet Form rather than a List Box. Then you could have linked the two Forms together with the Link Master Fields and Link Child Fields properties of the SubForm Control.

Because you have used a List Box within your SubForm for displaying and selecting records, things are a wee bit more difficult to tie the two Forms together. You will need to do it with code.

Don't get me wrong here...In my opinion there is absolutely nothing wrong with the way you have done it (it's actually safer that way as far as at the User end point is concerned), just a little more work and perhaps displays a little more glitter than necessary.

To get this to work you will need to place the SubForm (frmMain) into Design View and add this code to the lboCustomers List Box control OnClick event so it looks like this:

Private Sub lboCustomers_Click()
   Forms("frmQuoteLedger").Filter = "[JobNumber] = " & _
        Me.lboCustomers.Column(0, (Me.lboCustomers.ItemData(Me.lboCustomers.ListIndex) - 1))
   Forms("frmQuoteLedger").FilterOn = True
End Sub

That should get your project moving forward again.

.

 

[Rince]

Cheers Mate

Thanks alot CyberLynx that worked like a charm.
Also very impressed with the speedy response.

Thats the database complete. My standard access search function no longer works but thats not a problem as the user will search using the listbox sort buttons and using your code they can select a record.

Thanks again CyberLynx

Sean