dlookup with multiple criteria

[trixxnixon]

Foreign key from another table, and a primary key from the one being used.

i am still sort of a novice to access programming,

I have “tblMain” with a primary key “tracking number”
“tracking number” has a one to many with the table “tblPend”
“tblPend” has a primary key “RecordNo” and a status check box “status”

Status will either be marked yes or no.

All of these fields are on a form “frmPend”



This form opens from another form, but carries the tracking number from a field on the previous form. A subform at the bottom of the form runs a query “QryPend” and brings up all records in “tblPend” that have the referenced tracking number.

What I want the form to do is take the referenced tracking number, and open the first record with a status of yes in the form.


i can get the dlookup to work, but it looks it up for all tracking numbers not the tracking number specific to the form.

Can anyone help?

[jonnyboy]

Hi,

What do you mean by the first record? Is it the first created, or the first in the record sequence? I think you may need to have an intermediate query that identifies the specific RecordNo you require from the "tblPend" table.

Let me know how you get on.

[trixxnixon]

@jonnyboy
but the specific record that is required is going to be based on what data is listed in the form.
how would i get the form to run the query using data specified in the form?

[trixxnixon]

@jonnyboy
ok i have been able to get a query to search and bring up the record i need, as well as a dlookup to get the specific record number to open in the form.

the problem is, these forms are unbound and do not actually populate the fields with the data i need.

i need the form to get the matching the record number and open that record.

[NeoPa]

Is tblPend.Status a boolean (Yes/No) or a string field?

What is the name of the control on your form which contains the Tracking Number you want to find in your DLookup()?

[trixxnixon]

@NeoPa
i have 2 fields for tracking records in 2 different tables,
[tracking number] which is the primary key in tblMain which has a 1 to many with tblPend, whos primary key is [trackingno]

yes it is a check box that returns a value of yes or no,

this is what i have so far,
i have a query that runs based on the tracking number thats entered in the form, and a dlookup that looks up the first value with a status of "yes".
it searches the records that match the [tracking number] in the form with a status of "yes", and outputs the [recordno] into a non bound field.

i want the form to open the record for the specific [recordno] returned. the [recordno] field is an auto number.

i need to take that record no, and have the form populate with the data saved in that record.

also if no records match, then it should open ready to create a new record.

does this make sense? i feel like im still to novice to asq my questions correctly.

[NeoPa]

I don't feel I can sort your whole situation out for you as there is frankly too much information here that isn't quite consistent. I don't mean to be critical, because it's perfectly understandable that there are issues you don't fully understand. It does however, make it particularly complicated for people like me who have details and precision as the makings of our comfort zones.

I was proposing to post a (relatively) simple, multi-criteria, DLookup() example that you could build on. For that I need answers to my two questions (I do see your answers). For the answers to make sense though, I should explain that tblPend.Status is a field (found in a record) and not a control (found on an form - or report). The Tracking Number name I am after is conversely a control name, and not either of the two related field names you've provided.

Let's get this simple part done first and then (maybe) we can consider progressing on from there in small stages.

PS. Please don't think I'm trying to belittle your efforts so far. I do really acknowledge that you have tried to respond as asked, and that is the main thing I look for.

PPS. I stumbled across your response almost by accident. I didn't get notification of a reply, even though it's a subscribed thread. If you notice a large delay in future (>24 hours) feel free to bump the thread so I should notice it again. Bear in mind Christmas though, of course. I hope not to be available too much over the holidays - otherwise my life is getting just too sad.

[trixxnixon]

Just for the record, I am always humbled by the politeness displayed on this board. Thank you,


First I will give you a high level description of what I am trying to accomplish.

As I said before I have 2 tables, the main table whos records are distinguished by a tracking number
And a pending record table that serves the purpose of creating pending records for the records in the main table(this also has a primary key called “recordno”).

The records from the main table are made through forms the client uses, and processed through forms the specialist uses, which open up the information entered by the client plus additional fields the specialists need.

Sometimes these records need to be pended for approval. I am trying to automate this part of the application.

I have created a form (frmpend). This form currently transfers the tracking number from the specialist form, and opens the pend form with the tracking number field populated.

It also contains a “start pend date”, “end pend date” field, along with a status check box. All of these are controlled by one button. When the start date is populated, the check box( checked when file is pended )is also populated. When the end date is populated, the check box is cleared.

What I want this form to do, is search for any records that are currently marked as pended, and open them up with the form for editing. If no records are found, a new record will be created.

It should only be looking for records based on the tracking number carried over from the previous form, as well as a status marked as yes.

If I am able to set it up this way, only one record per tracking number can be marked as “yes” at one time.

I do have a query that searches for the tracking number and a status if “yes”. I was also able to use the dlookup to retrieve the first “recordno” of a pending record marked “yes” for a given tracking number.


Here are the tables and forms info,

tables
tblMain: Tracking Number,(there is quite a bit of other information on this table, but none of it is necessary for this part of the application.) tblPend: Recordno, start pend date, end pend date, status, tracking number,

forms

frmSpecialist: button(pending information)


frmPend: tracking number, recordno, start pend date, end pend date, button(pend/unpend), status(checkbox)


would I be able to take the record number produced by the dlookup and open up that record? Or should I choose an alternate method?