Not sure what is wrong here

[Ben]

Hi all,

I have some front end code calling the backend. I have a status table in
the back end, I use it for look up. If a record exists, then I won't create
a new table in the backend with that name, but if a record does not exist in
the backend status table, then I will create a new table in the backend .

Here's the problem: as soon as I finish the line rsNew.Update, I call the
CreateNew routine. In this routine, the new record should show up in the
table after the update statement, but it does not for some strange reason.
As I run through the FOR EACH loop, it never show up and hence the table
never get created because, I am running a check against all the tables names,
and if there’s no match, the flags remains false and base on that I would
create the table.

I don’t know if if it’s the Access event model, ie, the sequence of event
firing, that is causing the problem…can you help? The Call LinkNewTblToFE
just creates the link back to the front end.

Much appreciated.

Ben


Private Sub cmdAddNew_Click()
DoCmd.SetWarnings False
rsNew.AddNew
rsNew.Fields("Code").Value = Form_frm_AddNew.txtCode
rsNew.Fields("Name").Value = Form_frm_AddNew.txtName
rsNew.Fields("Status").Value = Form_frm_AddNew.txtStatus
rsNew.Update

Call CreateNew
DoCmd.Close acForm, "frm_AddNew"
Set rsNew = Nothing
End Sub

-----------------------------------------------------------------------------------------

Sub CreateNew()

Dim db As dao.Database
Dim tdfnew As dao.TableDef
Dim tdfs As dao.TableDefs
Dim rs As dao.Recordset
Dim fld As dao.Field
Dim strSQL As String
Dim intRecCount As Integer
Dim bTblExist As Boolean

Set db = OpenDatabase(strBE_FilePath)
Set tdfs = db.TableDefs

strSQL = "SELECT Code FROM tbl_Status "
Set rs = db.OpenRecordset(strSQL)
intRecCount = rs.RecordCount
rs.MoveLast
rs.MoveFirst

While Not rs.EOF
'//if the the table entry is already in the Status table is already in
it as a record, then
'//don't create it, set the flag to false
For Each tdfnew In tdfs
Debug.Print rs("Code") & "|" & tdfnew.Name & "|" & bTblExist
If tdfnew.Name = rs("Code") Then
bTblExist = True
Exit For
End If
Next

If Not bTblExist Then
Set tdfnew = db.CreateTableDef(rs("Code"))
With tdfnew
'add fields and create the table

db.TableDefs.Append tdfnew
End With
End If
bTblExist = False
rs.MoveNext
Wend

Call LinkNewTblToFE

Set db = Nothing
Set tdfnew = Nothing
Set tdfs = Nothing
Set rs = Nothing

End Sub




--

 

[Clifford Bass]

Hi Ben,

Is there more to your cmdAddNew_Click routine? What is this rsNew?
Where does it get opened? Instead of turning warnings off, how about
including some error handling code so that you can see if you are getting an
error somewhere?

An even more basic question: Why even do it that way? Just make your
frm_AddNew form use with tbl_Status as its recordset. Then in the place
where you open the form, open it in add mode. After the user closes the form
then call your second routine.

Hope this helps,

Clifford Bass

Hi all,

I have some front end code calling the backend. I have a status table in
the back end, I use it for look up. If a record exists, then I won't create
a new table in the backend with that name, but if a record does not exist in
the backend status table, then I will create a new table in the backend .

Here's the problem: as soon as I finish the line rsNew.Update, I call the
CreateNew routine. In this routine, the new record should show up in the
table after the update statement, but it does not for some strange reason.
As I run through the FOR EACH loop, it never show up and hence the table
never get created because, I am running a check against all the tables names,
and if there’s no match, the flags remains false and base on that I would
create the table.

I don’t know if if it’s the Access event model, ie, the sequence of event
firing, that is causing the problem…can you help? The Call LinkNewTblToFE
just creates the link back to the front end.

Much appreciated.

Ben

[snip]

 

[Ben]

Cliff,

I took some of your suggestions and I re-ordered the event code. As it
turned out, it was the event code that was the problem. Thanks so much for
reading through my long post.

Regarding the rsNew, yes, it is created somewhere else, in fact that
recordset is just as you said, it is based on the data in the table, but I
populated it using an SQL rather than by table.

Again, thanks so much for your help.

Ben

--

Hi Ben,

Is there more to your cmdAddNew_Click routine? What is this rsNew?
Where does it get opened? Instead of turning warnings off, how about
including some error handling code so that you can see if you are getting an
error somewhere?

An even more basic question: Why even do it that way? Just make your
frm_AddNew form use with tbl_Status as its recordset. Then in the place
where you open the form, open it in add mode. After the user closes the form
then call your second routine.

Hope this helps,

Clifford Bass

Hi all,

I have some front end code calling the backend. I have a status table in
the back end, I use it for look up. If a record exists, then I won't create
a new table in the backend with that name, but if a record does not exist in
the backend status table, then I will create a new table in the backend .

Here's the problem: as soon as I finish the line rsNew.Update, I call the
CreateNew routine. In this routine, the new record should show up in the
table after the update statement, but it does not for some strange reason.
As I run through the FOR EACH loop, it never show up and hence the table
never get created because, I am running a check against all the tables names,
and if there’s no match, the flags remains false and base on that I would
create the table.

I don’t know if if it’s the Access event model, ie, the sequence of event
firing, that is causing the problem…can you help? The Call LinkNewTblToFE
just creates the link back to the front end.

Much appreciated.

Ben

[snip]

 

[Clifford Bass]

Hi Ben,

Glad to be of help and you are welcome!

Clifford Bass