Find second set of records in a form

[jrf]

I give up. Something that seems so simple is complete eluding me.

I am creating a form for entering timeslips. We have claimant records and each claimant can have one to several authorizations.

I have created a form that looks up the claimant records perfectly. I can find each and every specific claimant thru a combo box lookup.

The problem is the authorizations. I am using a second combo box so that I can attempt to select between all the authorizations for each claimant. I have an authorization query that orders them correctly with each claimant's most recent authorization popping up first. I have no problem with a subform for this working just find but I cannot call out specific items on the subform so I go looking with a combo box.

I would like the combo box to show the most recent authorization code on each change of claimant on the form but only get empty boxes. I cannot make the combo box look for the authorizations. If I call out the WHERE qryauths.fldclaimno = combo6.column(1) I get an error message that there is no reference to "combo6.column". If I do it with qryauths.fldclaimno = qryclaimants.fldclaimno
I get a message box that asks me to manually enter the contents of qryclaimants.fldclaimno. If I enter the claimant number that I am looking at on the form, specifically combo6.column(1) and also filling a text box fo qryclaiamnts.fldclaimno, the combo box then works perfectly except I have to click on the down arrow to see any contents. It appears that the sql search does not reconize any info on the form.

Any help would be appreciated. Don't worry about the syntax above, when entering the queriers, etc, I do it right or let the wizards do it form me, the [qryclaimants]![fldclaimno], etc.

Thanks

 

[RickSpr]

It's hard to follow your description. I'm not sure where this WHERE clause is that you're referring to.

But I gather what you want to do is cause the authorizations combo box list to change when a claimant is selected. You need to use the claimant combo box's AfterUpdate event. It can do one of two things. First, it can build an SQL statement that selects the correct authorizations, and assign the statement directly to the RowSource property of the authorizations combo box:

    cboAuths.RowSource = strAuthsQuery

Or second, you can use a pre-built query as the row source for the authorizations combo box, and let the claimant's AfterUpdate call the authorizations combo box's Requery method:

    cboAuths.Requery

In this case, the pre-built query would refer to the value of the claimant combo box on the form:

    ...WHERE (qryauths.fldclaimno = qryclaimants.fldclaimno) AND (qryclaimants.fldclaimant = Forms!PayslipEntry!cboClaimants)

The second method sounds closer to what you're describing, but has the disadvantage that your row source query depends on the form being opened. If you open the query without the form, it will prompt you to enter a value for Forms!PayslipEntry!cboClaimants.

By the way, you don't have to have the [] brackets around a table, field, form, report, or control name unless it contains a character (usually a space) that would confuse the syntax. Single-word names that start with a letter and don't match VBA keywords can be used without the brackets. I often put them in anyway, as a way to distinguish them from variable names. Rick Sprague