Dlookup Criteria problem 3

[jw5107]

I have a report that perfoms a Dlookup function.
This is what I got:
=DLookUp("Allocations","GTWYAllocatedDelay","!IID = [IID]")

In the criteria section of the lookup - it does the look up based on the IID field in the report and IID field in the GTWYAllocatedDelay table. I need for the lookup to be based on the IID field in the report and the IID field in the table AS WELL AS the GTWY field in the report AND the GTWY field in the table...

Any help with this. Not sure how it needs to be setup.

Thanks,
jw5107

 

[dhookom]

Why is there a "!" in your expression? Do you want "<>"?
Your answer depends on the data types of your fields. If IID is numeric:
=DLookUp("Allocations","GTWYAllocatedDelay","IID <> " & [IID])
If it is text:
=DLookUp("Allocations","GTWYAllocatedDelay","IID <> """ & [IID] & """")

If you want multiple field comparisons:
=DLookUp("Allocations","GTWYAllocatedDelay","IID <> " & [IID] & " AND GTWY=""" & [GTWY] & """")



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jw5107]

Dhookum,

Thank you very much for the fix!!! Once again you have bailed me out!!!

If I could trouble you some more... I am having troubles with a Main form/Sub form scenario.

Below is code I retrieved:

Private Sub Command75_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.OpenForm "Copy of MORNING", , , acNew
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "Copy of MORNING"
End Sub

This code takes the current record of the main form ONLY and adds it to another form as a new record. I would like for this to work with the current record on the on the subform as well. Ending up with the current records from the main and subform as a complete new record in another form. How do I get this code to pull the current record from the subform at the same time??

Also - when the code is ran - I keep getting an error that states some of the field names don't match. I don't want to save EVERY field as an new record, just a select few...
Can I get an example of how this could be done??

Thanks in advance!!!!
jw5107

 

[dhookom]

I don't believe in using copy and paste like this. I would write code to insert values into a table like:
strSQL = "INSERT INTO tblB (FieldA, FieldB,...) " & _
"Values (" & Me.txtFieldA & ", " & Me.txtFieldB & "...)"
DoCmd.RunSQL strSQL

Then, open the "copy of morning" form filtered to the newly added record.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jw5107]

dhookum,

Thanks for the fast response!!

Does this work with current record on the main form and subform?

Do I need to refer to the names of the main form and subform?

I don't quite understand Me.txtfieldA ???? Is this the field name of the table I am pasting to, or the txtbox on the main form and subform?

Thanks,
jw

 

[dhookom]

Me.txtFieldA refers to a value in a text box on the current record on the current form. Try not to think of copying in pasting. You might want to think of appending a record from a source table to a target table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jw5107]

Dhookom,

This is what I am working with per your suggestion:

Private Sub Command85_Click()
strSQL = "INSERT INTO Copy of MORNING (EX CODE, DATE,GTWY, TAIL, MPN) " & _
"Values (" & Me.EXCD & ", " & Me.DATE & ")"
DoCmd.RunSQL strSQL
End Sub

I keep getting a "variable not defined" error message. Kinda lost on this one. Any fixes??

Do I need to have the txtbox names the same as the tbl I am pasting to? Will this code copy the current record from the subform as well? Just list all the txtbox names...???

Thanks,
jw

 

[dhookom]

Several issues with:
strSQL = "INSERT INTO Copy of MORNING (EX CODE, DATE,GTWY, TAIL, MPN) " & _
"Values (" & Me.EXCD & ", " & Me.DATE & ")"
1) Copy of MORNING has two spaces so you need [Copy of MORNING]
2) EX CODE has a space so you need [EX CODE]. Don't use spaces in object names...
3)You have 5 fields in the target and only two in the Values.
4) You have a field named Date which is also a function name. (not always a problem but bad practice)
5) When inserting date values, you must delimit them with "#"
Values (" & Me.EXCD & ",#" & Me.Date & "#...
String values must be delimited with Quotations Marks.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

can someone tell me what I am doing wrong?

I have a table that carries the FK from another table.

On the report, I want the productname from the product table to be displayed. I have the productfk field in the report record source. I know I have this DLookup wrong, but hoping you can help.

=DLookUp("Product","Product Main",[Product FK]=[Product Main]!PK)

misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

I would just include the [Product Main] table in the report's record source. You would join the key fields and add [Product] to the grid. Then add the field to your report.

DLookup() is horribly slow. If you really want to use it:
=DLookUp("Product","Product Main","[PK]=" & [Product FK])
This assumes the field is numeric. If PK is text then try:
=DLookUp("Product","Product Main","[PK]=""" & [Product FK] & """")


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[misscrf]

Thanks! I had the product table in the underlying record source, but I also had the product fk in the query fields. I took that out, and just put the product field in ,and now it runs great!


Thanks a bunch dhookom!


misscrf

Management is doing things right, leadership is doing the right things

 

[pduncan]

I am using the dlookup function to try to find a region number based on a branch number. The region. For some reason, I am getting the wrong answer returned.

Here is the SQL from my query:
UPDATE tblInvoice INNER JOIN tblRegion ON tblInvoice.ARSLoc = tblRegion.BranchNo SET tblInvoice.RegionNo = DLookUp("[Region]","tblRegion",tblInvoice!ARSLoc=tblRegion!BranchNo);

Any suggestions?

 

[misscrf]

This probably isnt it, but just for the heck of it, try changing the = around....
UPDATE tblInvoice INNER JOIN tblRegion ON tblInvoice.ARSLoc = tblRegion.BranchNo SET tblInvoice.RegionNo = DLookUp("[Region]","tblRegion",tblRegion!BranchNo=tblInvoice!ARSLoc)


misscrf

Management is doing things right, leadership is doing the right things

 

[dhookom]

The where clause of your DLookup() must be a string expression. I think this is what you need.

If BranchNo in tblRegion is numeric:
DLookUp("[Region]","tblRegion","BranchNo=" & tblInvoice!ARSLoc)

If BranchNo in tblRegion is text:
DLookUp("[Region]","tblRegion","BranchNo=""" & tblInvoice!ARSLoc & """")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pduncan]

misscrf - that didnt do it, but thanks very much anyway.

Duane, BranchNo in tblRegion is text, and your solution worked perfectly. I have spent a good deal of time trying to figure out what I did wrong, but don't understand what I was doing wrong.
Here is a comparison:

Right:
DLookUp("[Region]","tblRegion","BranchNo=""" & tblInvoice!ARSLoc & """")

Wrong:
DLookUp("[Region]","tblRegion",tblInvoice!ARSLoc=tblRegion!BranchNo)

I understand about the where clause needing to be a string expression (which I didn't do) - but I am having trouble how your reversal of the items on either side of the equal sign was also part of the solution. Can you please explain this to me so that I wont make this mistake again?

Thanks again!

 

[dhookom]

Everything in the quotes is either the lookup table or one of its fields. tblInvoice.ARSLoc is not in tblRegion so it can't be in the quotes.

The expression is stating:
Find the Region field value in the table tblRegion where the BranchNo field in tblRegion equals this other field from my query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.