Access 2010 Get Specific Record from Table

[sahm]

Hi,
I have 2 Tables. Table "BOL" and Table "Containers". Both contain "Job_Number" and "Bill_of_Lading_Number" as a field. I would like to get a "Bill_of_Lading_Number" record from "BOL" and add it to "Bill_of_Lading_Number" in "Containers" table based on condition when both "Job_Number" are equal.

I have tried two methods. One with DLookup and another with SELECT. Both work but with a small problem.

When I execute them, a dialogue box popup asking me to enter the "Bill_of_Lading_Number" value while the required value shows as dialogue box heading. This box shouldn't come up and I am not sure why this is happening.

P.S. "varsJobCont" variable I get from another form and is "Job_Number" value that determines the record to be selected.

My Both Codes are:


=========================
Dim strBill As String

strBill = DLookup("Bill_of_Lading_Number", "BOL", "Job_Number = " & Application.TempVars("varsJobCont").Value)


DoCmd.RunSQL "Update Containers SET Bill_of_Lading_Number = " & strBill & " WHERE Job_Number = " & Application.TempVars("varsJobCont").Value


==========================
Dim strBill As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT BOL.Bill_of_Lading_Number FROM BOL WHERE BOL.Job_Number = " & Application.TempVars("varsJobCont").Value)

hello = rst.Fields(0).Value


DoCmd.RunSQL "Update Containers SET Bill_of_Lading_Number = " & strBill & " WHERE Job_Number = " & Application.TempVars("varsJobCont").Value

rst.Close
Set rst = Nothing
Set dbs = Nothing

======================================


Attached image below shows the dialogue box and the heading "bbb1055" is actually the correct value I needed to get. It should have been put in the other table automatically without the need to insert it manually again.


Any idea why this happening.

Thanks.

[ItsMe]

Maybe something like this?

Dim strBill As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT BOL.Bill_of_Lading_Number, BOL.Job_Number FROM BOL WHERE BOL.Job_Number = " & Application.TempVars("varsJobCont").Value)
If rst.EOF = False Then
strBill = rst![Bill_of_Lading_Number]
DoCmd.RunSQL "Update Containers SET Bill_of_Lading_Number = " & strBill
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

[sahm]

Thanks ItsMe.

Unfortunately, the same problem exist and also it updates all the records because you removed the condition of updating only the matching record.

Any other ideas.

Thanks.

[June7]

Why do you need to duplicate the Bill_of_Lading_Number in both tables?

That violates basic relational database principle of not duplicating data.

Why not just build a SELECT query that joins the two tables on the Job_Number?

What are relationships of this data? Will each job number have only one bill of lading and only one container? If so this could be a single table.

[sahm]

Thanks June7

I need it for other purposes within the application.

There should be a straight forward method to copy the value of a field from one table to another.

Thanks again.

[ItsMe]

You can add the WHERE criteria back to your action query.
DoCmd.RunSQL "Update Containers SET Bill_of_Lading_Number = " & strBill & " WHERE Job_Number = " & Application.TempVars("varsJobCont").Value

Not sure why you are being asked for a parameter. If a field was missing from the SQL then the error would say that, specifically. When you say, "value"... that does not make any sense either. The only thing I can imagine is you are changing a value for an existing field that has a referential integrity rule that is being broken by not updating the related record in another table.

When I look at the error msg I would guess you are trying to bind a control to a field that does not exist in the form's recordset.

So, if the action query is running and updating all the records AND you are getting the warning msg like the one in the image you posted, something else is going on. And I mean there is some other code or user interface that is causing the error.

[June7]

What is this purpose elsewhere in application? Why would a SELECT JOIN query not serve?

If these tables are related by the Job Number, why bother with the restriction, just update all.

UPDATE Containers LEFT JOIN BOL ON Containers.JobNum = BOL.JobNum SET Containers.Bill_of_Lading = [BOL].[Bill_of_Lading];

[sahm]

Actually the Job can have more than one Bill_of_Lading_Number and more than one container. I think I need it to do a proper costing but maybe as you said I will not need it.

I think I will explore your ideas and come back again.

By the way, if I assign a fixed value to strBill like for example strBill="11111", then the code works perfect and it updates only the targeted record with same Job_Number without the box coming up.

Thanks guys for the help.

[ItsMe]

Perhaps your tempvars contains more than one value and when you assign the tempvar to a string variable it is not the appropriate data type for the field you are trying to update.

What data type is the field Bill_of_Lading_Number ?

[June7]

Seems to me if job number can have multiple containers and multiple bills, then running an update with only the job number as filter criteria will update ALL records for that job number with the same bill.

[sahm]

The tempvars "varsJobCont" is an integer and holds the "Job_Number" value which is a an integer field in the table and is used for selection condition.

The "Bill_of_Lading_Number" field in table is a Text field and is not a PK.

The record actually exist in Table with only "Job_Number" from a previous action. Now all is needed is to update the "Bill_of_Lading_Number" with a value.

thanks.

[ItsMe]

Seems to me if job number can have multiple containers and multiple bills, then running an update with only the job number as filter criteria will update ALL records for that job number with the same bill.

Yah, I am still not understanding the objective. Looking at the code examples provided by the OP cause me to think there may be a better approach but...

sahm,

What happens if you comment out all of your original code and try to debug.print your tempvar.

on error resume next
debug.print "Tempvar = " & Application.TempVars("varsJobCont").Value
Dim varFind as variant
for each varFind in Application.TempVars("varsJobCont")
debug.print "varFind = " & varFind
next

Something is going on and it is hard to decipher because we are not seeing enough of the big picture. I feel that all I can do is look at the code and guess what might be wrong. In the end, the code could probably be refactored and I believe this is what June is trying to accomplish by using the correct join with the existing data.

[sahm]

Many thanks guys for the help.

I found a slightly different way to do it.

Dim strBill As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT BOL.* FROM BOL WHERE BOL.Job_Number = " & Me!Job_Number)

Bill_of_Lading_Number.Value = rst.Fields(2).Value


rst.Close
Set rst = Nothing
Set dbs = Nothing

This works fine.

I have a related question but I think I will open a new thread for the benefit of the others.

Many thanks again.
Sahm

[ItsMe]

Glad you were able to get it resolved.