Passing a Default Value to a New Record

[chappie2]

My db has two relevant tables, tblGrantee and tblGrantSum. TblGrantSum is a child of tblGrantee with DLCDGrant# as the primary and foreign key in the tables. Both tables have a "StreetAddress" field in them because the same street address is not always used by a Grantee.

My form simply lists the Grantee and DLCDGrant# fields. Below is coding I use on a command button to create a new record in tblGrantSum and open a more complete data entry screen. As a part of the process, I would like the value of StreetAddress in tblGrantee, to be added to the StreetAddress field in the new GrantSum record. I have tried setting default values in the control and tables and some coding options, but I cannot get it to "take". Anyone provide me guidance?

Thanks. Chappie

Private Sub cmdNewRecordDetail_Click()
On Error GoTo Err_cmdNewRecordDetail_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGrantSumDE"

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

stLinkCriteria = "[DLCDGrant#]=" & "'" & Me![DLCDGrant#] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdNewRecordDetail_Click:
Exit Sub

Err_cmdNewRecordDetail_Click:
MsgBox Err.Description
Resume Exit_cmdNewRecordDetail_Click

End Sub

 

[kjv1611]

Howabout using 2 DAO.Recordsets - one for each table, and copy the correct entries that way? Using DLookup for finding the correct record?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[chappie2]

Thanks Steven. I'm willing to try DAO.recordsets, but need some basic guidance about how to set that up. Can you suggest some coding and how it would relate to the coding I already have?

Thanks, Chappie

 

[kjv1611]

Private Sub cmbAddress_AfterUpdate()
  Dim db as DAO.Database
  Dim rsTable1 as DAO.Recordset
  Dim rsTable2 as DAO.Recordset
  Set db = CurrentDb
  Set rsTable1 = db.OpenRecordset("tblTable1")
  Set rsTable2 = db.OpenRecordset("tblTable2")

  Do While Not rsTable1.EOF
    If DLookup("[Address]", "tblTable2", "[Address] = '" & cmbAddress & "'") Then
      rsTable2.Fields("Address") = rsTable1.Fields("Address")
      Exit Do
    End If
  Loop
  
  Set rsTable1 = Nothing
  Set rsTable2 = Nothing
  Set db = Nothing
  rsTable1.Close
  rsTable2.Close
  db.Close
End Sub

Well, something like this.. you may have to switch which table use use in the Dlookup, and which in the loop, just make them match the tables you are using.. with a little tweaking to your specific situation, I believe it would work perfectly... you may not even need the Do While Loop, I've just used that for many things, and threw that in there.. I've been using the Dlookup and DAO.Recordset stuff for several purposes as of late, and found much help when starting here at tek-tips.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Just be sure to post back with progress, and if have any problems, errors, post the code and the error you are having with that code..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[chappie2]

I have worked with your coding, but can't make it work. A couple of issues;

1) I had intended to associate this with click on a command button which opened a new form - so would I simply add your coding to the correct private sub? Where would the Do Loop go relative to the Save Record and Open Form commands in my coding?

2) Your coding lists a "cmbAddress" in the DLookUp function. I think this is a part of my problem. If I understand DLookUp, this is a part of the criteria for deciding which street address to select. It would seem that I all want to know is if the value of the StreetAddress field is "blank"? But I can't seem to get the coding correct.

Thanks,

Chappie

 

[kjv1611]

1) I don't see where it would matter, so long as you are not updating the same record.

2) If you want DLookup to see when the field StreetAddress is Blank, then you just need to add IsNull to the dlookup statement like this:

If IsNull(DLookup(...)) Then

Or if you are just wanting find any records that have a blank in that field, then maybe you could do it with a SQL string?

Also, the cmbAddress I was just using as an example - it could be a combo box holding your address value..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

And, maybe I misunderstood?? But I was assuming you have a text box or combo box of some sort that has it's ControlSource set to one table, but you also want to update a second table with the same address? I'll look at this some more myself, and see if I see anything different...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Okay, I think this would be another way to do it - if you want to update the second table per the first table, when the second table field "address.." is blank.. Using the same example above without the Dlookup..
Private Sub cmbAddress_AfterUpdate()
Dim db as DAO.Database
Dim rsTable1 as DAO.Recordset
Dim rsTable2 as DAO.Recordset
Set db = CurrentDb
Set rsTable1 = db.OpenRecordset("tblTable1")
Set rsTable2 = db.OpenRecordset("tblTable2")

Do While Not rsTable1.EOF
Do While Not rsTable2.EOF
If rsTable2.Fields("RecordID") = rsTable1.Fields("RecordID") Then
If IsNull(rsTable2.Fields("Address")) Then
rsTable2.Edit
rsTable2.Fields("Address") = rsTable1.Fields("Address")
rsTable2.Update
Exit Do
End If
End If
rsTable2.MoveNext
Loop
rsTable1.MoveNext
Loop
rsTable1.Close
Set rsTable1 = Nothing
rsTable2.Close
Set rsTable2 = Nothing
db.Close
Set db = Nothing
End Sub

See if that does it for you, of course correcting the exact context for your project... I still have not looked much at the whole deal, just thought this could be a better way around??


Stephen         [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[chappie2]

Your approach may work Steve, but I am not familiar enough with VB to understand what it is doing and why?

As an alternative, could I simply open the newly created record and include a command button which would update any of several fields which were blank? This seems more straight forward to me. But coding the If IsNull and/or DLookUp confuses me as well.

For example, I have made the field [tblGrantee.StreetAddress] invisible on my form frmGrantSumDE and the field [tblGrantSum.StreetAddress] visible. Both fields are listed in my query, qryGrantSumAll. Could I use an update button to verify if [tblGrantSum.StreetAddress] is blank and if so, replace the blank value with [tblGrantee.StreetAddress]? Can you show me the coding to do that?

Sorry for this change in direction, just felt like the other approach was headed way, way over my head!!

Thanks, Chappie

 

[kjv1611]

I am assuming you mean you have controls (text boxes or such) linked to each table on the same form. So, for instance, if you update Address for one table, you want it to update another textbox which will in turn update the second table. Is this correct?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[chappie2]

That's right. I have text boxes for the various fields from each table on the form.

Chapppie.

 

[kjv1611]

Well, then for the ones that you are updating manually, you can put code in their _AfterUpdate() events that will set the values of the other text boxes to these values, and thus update the other table. Like this:

Private Sub TextBox1Table1_AfterUpdate()
    TextBox1Table2 = TextBox1Table1
End Sub

That should be all there is to it, unless some other code is needed to save the record in the other table, but I would think that will happen automatically.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV