Help for correcting Before Update

[Pragmatic]

Hi all,

I have the following code on my form "Matter".


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT count(tblClientMatter.MatterID) as TotRec FROM tblClientMatter WHERE (tblClientMatter.ClientID)= '" & Me.ClientID & "'"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Me!MatterID = "WO-" & rs!TotRec + 1
Me!MatterID = rs!TotRec + 1



Set rs = Nothing
End Sub

When I click the Add Matter button on my "Client Form" it opens up the matter form, although it displays client id on matter form but does not display/generate the matter id until I save the record is there way it can display the matter id as well on opening?

Any help suggestion will be highly appreciated.

[ssanfu]

Is "ClientID" text or a long integer? If it is a long, you don't need delimiters.

You have two lines setting Me!MatterID. The second line changes the first:

Me!MatterID = "WO-" & rs!TotRec + 1
Me!MatterID = rs!TotRec + 1

Try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim strSQL As String

  Set db = CurrentDb
  strSQL = "SELECT count(tblClientMatter.MatterID) as TotRec"
  strSQL = strSQL & " FROM tblClientMatter"
  strSQL = strSQL & " WHERE (tblClientMatter.ClientID)= '" & Me.ClientID & "'"

  Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

  Me!MatterID = "WO-" & rs!TotRec + 1
  Me!MatterID = rs!TotRec + 1     ' Why two lines setting Me!MatterID  ???

  Me.Repaint  '<<- added

  Set rs = Nothing
End Sub

[Pragmatic]

Thank you very much for your reply and providing the amended code. I have deleted the extra line but it still does not display/generate the MatterID on opening the "Matter Form" . Is there way when matter form open it show matterid and clientid both. Both "ClientID" and "MatterID" are text fields.

[ssanfu]

Thank you very much for your reply and providing the amended code. I have deleted the extra line but it still does not display/generate the MatterID on opening the "Matter Form" . Is there way when matter form open it show matterid and clientid both. Both "ClientID" and "MatterID" are text fields.

Would you post your MDB? Do a "Compile and Repair", then zip it.

[Pragmatic]

Please find attached MDB. Try to open formtwo as stand alone as well please and if you find any error message please advise how to fix it.

[Pragmatic]

Would you post your MDB? Do a "Compile and Repair", then zip it.

File is attached please.

[ssanfu]

File is attached please.

Had to wait until I got home to look at it - the file is not in A2K format.

Finally was able to open the MDB. Still trying to understand your MDB.

Why do you have ClientID as a text type. Most of the time, the PK is an autonumber (long int).

Still looking at it and trying to get form two to open up with the correct ClientID.

[ssanfu]

Still looking at it.... hang in there

[Pragmatic]

Still looking at it.... hang in there

How long do I have to wait. Is this something unusual?

[ssanfu]

How long do I have to wait. Is this something unusual?

Well, there is work, the holidays, family obligations and shoveling snow..... I'm still looking at it when I can make time....

[Pragmatic]

Well, there is work, the holidays, family obligations and shoveling snow..... I'm still looking at it when I can make time....

Hope you will come up with solution....

[ssanfu]

Hope you will come up with solution....

Couldn't come up with a solution with your structure, so I changed some field types and links. See if this will work for you.

Also, I think it would be easier, if not better, to have "frmTwo" as a subform of "frmOne", instead of a pop up form.


BTW, in the client table, I wouldn't have the fields that end in "_2". This violates normalization rules (but I didn't change it).