Ties that don't bind... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Ties that don't bind...

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-07 : 11:33:50

I have a situation in the creation of an entry form in MS Access where once I click the "Submit Records" (activating the Command45_Click() subroutine, it writes the record then refuses my attempts on the form to make another entry.

I thought I'd cleared that up by adding the "Insert into OPA (the table)", but I still get the message "Control cannot be editted; it's bound to unknown field". Apparently, my form is coming unconnected from the table.

Here's my code. Any help you offer would be greatly appreciated!

Private Sub Command45_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT OPA.[Rec Type], OPA.Function, OPA.RID, OPA.[Process Dt], OPA.[Ref IHCP Num], OPA.[Prov IHCP Num], OPA.[Admit Dt], OPA.[Thru Dt], OPA.[Diag Cd 1], OPA.[Diag Cd 2], OPA.[Total Units], OPA.[Tot Appvd Units], OPA.[Tot Denied Units], OPA.[Status Reason], OPA.[Patient Status], OPA.POS, OPA.[Proc Code], OPA.[PA Auth Num], OPA.[Line Num], OPA.[Ref NPI], OPA.[Prov NPI] INTO TEMPOPA " + _
"FROM OPA"
Call fnWriteFile("TEMPOPA")
DoCmd.RunSQL "DROP TABLE TEMPOPA"
CurrentDb.Execute "DELETE FROM OPA", dbFailOnError
DoCmd.RunSQL "INSERT INTO OPA ([Rec Type]) VALUES('2')"
End Sub

Private Function fnWriteFile(strTab As String) As Boolean

Dim i As Integer
Dim rst As Recordset
Dim fso As Object
Dim Ftype As String

Me.RecordSource = ""
Set rst = CurrentDb.OpenRecordset(strTab)
Set fso = CreateObject("Scripting.FileSystemObject")
strDate = Format(Date, "yyyymmdd")
strTime = Format(Time, "hhmmss")
strHdr = "1" & strDate & strTime & " "

'If Not (rst.BOF And rst.EOF) Then

Set Textfile = fso.CreateTextFile("c:\HIP_PA_OutPat_METH_" & strDate & strTime & ".txt", True)
Textfile.WriteLine (strHdr)

Do Until rst.EOF = True
Textfile.WriteLine (rst![Rec Type] & rst![Function] & rst![RID] & rst![Process Dt] & rst![Ref IHCP Num] & rst![Prov IHCP Num] & rst![Admit Dt] & rst![Thru Dt] & rst![Diag Cd 1] & rst![Diag Cd 2] & rst![Total Units] & rst![Tot Appvd Units] & rst![Tot Denied Units] & rst![Status Reason] & rst![Patient Status] & rst!POS & rst![Proc Code] & rst![PA Auth Num] & rst![Line Num] & rst![Ref NPI] & rst![Prov NPI])
rst.MoveNext
i = i + 1
Loop

strTlr = "9" & Right("0000000000" & CInt(i), 6)
Textfile.WriteLine (strTlr)
Textfile.Close
'End If

Me.RecordSource = ""
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

End Function
Semper fi,

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-07 : 13:58:49

Me.RecordSource = "", why do you have that?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-07 : 14:07:40

Um.....seemed like a good idea at the time. Is it the cause?

(BTW....enjoyed the discussion on your new moniker!)

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-07 : 14:13:04

I think when you do Me.Recordsource, you disconnect it from the table. Try removing it.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-07 : 14:35:16

Thanks....I realize that it's those small, mesonic errors that can make the big project look like a large pile of hollerith cards!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources