AutoFill Form from table using criteria in the form

[ramez75]

Hi,

I have been struggling to get this to work. So hopefully someone can guide me or help me out with this

I have a table called tblSupplierAddresses with 2 columns "SupplierName" and "Address"

I have a form which has a subform and both are called "frmMatRRSubform" and "frmDMRSubform"

In form "frmMatRRSubform" I have a field called "shipto" which I want to autopopulate when the form is open using the name of the supplier (Supplier) in the subform called "frmDMRSubform"

So the logic works as follows when the form opens up by the user the VBA code will take the value of the field "Supplier" in the "frmDMRSubform" and go into the "tblSupplierAddresses" matching "Supplier" in "frmDMRSubform" with "SupplierName" in "tblSupplierAddresses" once the match is found the VBA returns the value in "Address" in tblSupplierAddresses" and paste it in the field "shipto" in "frmMatRRSubform"

I have created the below so far but it doesn't work. Can anyone look and tell me what is wrong or what am I missing

 Private Sub Form_Current()
  
 Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSupplier As String
  
 If IsNull(Me.shipto) Then
                          
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSupplierAddresses", dbOpenDynaset)
    
    strSupplier = DLookup("SupplierName", "tblSupplierAddresses", "[SupplierName]=" & Forms![frmMatRRSubform]![frmDMRSubform].Form.Supplier)
        
    Me.shipto.Value = rs!Address
    
    Set db = Nothing
    Set rs = Nothing
    
End If
End Sub

Thanks

RB

 

[pbaldy]

Have you tried using the variable you populated rather than the recordset? You'd need to use the text syntax I suspect:

http://www.mvps.org/access/general/gen0018.htm

The recordset could work, but would need to be opened with a criteria similar to the DLookup(). As is I assume it returns the first record.

 

[ramez75]

pbaldy, I am sorry I am not following can you elaborate more.

 

[pbaldy]

You set strSupplier but then you don't use it.

 

[ramez75]

How do i use strSupplier that's were I got stuck

 

[pbaldy]

Me.shipto.Value = strSupplier

 

[ramez75]

But the "shipto" should be auto filled by the value "Address" from tblSupplierAddresses if "Supplier" in frmDMRform = "SupplierName" in tblSupplierAddresses

 

[pbaldy]

Looks like you're looking up the wrong field. Perhaps:

strSupplier = DLookup("Address", "tblSupplierAddresses", "[SupplierName]='" & Forms![frmMatRRSubform]![frmDMRSubform].Form.Supplier & "'")

 

[Cronk]

Firstly,
strSupplier = DLookup("SupplierName", "tblSupplierAddresses", "[SupplierName]=" & Forms![frmMatRRSubform]![frmDMRSubform].Form.Supplier)

This won't work because if you had read the reference provided by Paul, you would put quotes around the string you are looking up.

Secondly, as Paul says what you are doing with this is looking up and returning in strSupplier, the name of the supplier you are looking up.

It should be dlookup("SupplierAddress","tblSupplierAddresses",.....)

 

[ramez75]

I fixed the quotation per the link pbaldy sent me.

Also thanks for catching my error

Below is the working code for anyone who might need it in the future

 Private Sub Form_Current()
 Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSupplier As String
 If IsNull(Me.shipto) Then
                                
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSupplierAddresses", dbOpenDynaset)
     strSupplier = DLookup("Address", "tblSupplierAddresses", "[SupplierName]='" & Forms![frmMatRRSubform]![frmDMRSubform].Form.Supplier & "'")
    
    Debug.Print strSupplier
    
    Me.shipto.Value = strSupplier
    
    Set db = Nothing
    Set rs = Nothing
    
End If

 End Sub

Thank you for all the help

RB

 

[pbaldy]

Happy to help. You realize the recordset isn't used, and all code related to it can be deleted?