将数据提交到sql表 [英] Submitting data to a sql table

问题描述

我的vba应用程序中有两个表单。我试图做的是一些事情。当用户点击我的主表单上的最后一个按钮时，会显示另一个表单，要求他们从选项组中选择值，然后在他们选择
该值时提交。从那里我遇到的问题是

1。我想将数据从主表单发布到sql数据库（我不知道该怎么做）

2。在数据提交到数据库时（我认为我有代码）和

3时对数据加上时间戳。当用户按下辅助表单上的提交按钮时，同一数据库将附加该表单中的值（1,2或3）（我不知道该怎么做）

以下是我的主要表格代码：

 
 选项比较数据库
 
 '---------- --------------------------------------------------  
 'Command0_Click  
 ' 
 < span style ="color：Green;">'--------------------------------------- ---------------------  
 私人  Sub  Command0_Click（）
  On  错误  GoTo  Command0_Click_Err 
 
 DoCmd.OpenQuery " 1_LogInScratchPad&quo t; ，acViewNormal，acEdit 
 DoCmd.OpenTable " 2_ScratchPad" ，acViewNormal，acEdit 
 
 
 Command0_Click_Exit：
 退出  Sub  
 
 Command0_Click_Err ：
 MsgBox 错误 $ 
 恢复 Command0_Click_Exit 
 
  End   Sub  
 
 '---------------------------------------------- --------------  
 'Command1_Click  
 ' 
 '------------------------- -----------------------------------  
 私人  Sub  Command1_Click（）
  On  错误  GoTo  Command1_Click_Err 
 
 
 DoCmd.OpenQuery " 2_ExceptionsScratchPad" ，acViewNormal，acEdit 
 DoCmd.OpenTable " 3_ExcepScratchPad" ，acViewNormal，acEdit 
 
 
 Command1_Click_Exit：
 退出  Sub  
 
 Command1_Click_Err：
 MsgBox 错误 $ 
 恢复 Command1_Click_Exit 
 
 结束  Sub  
 
 '------- -------------------------------------------------- ---  
 'Command2_Click  
 ' 
 '------------------------------------ ------------------------  
 私人  Sub  Command2_Click（）
 DoCmd.OpenForm "Form1"，acNormal，acEdit 
 < span style ="color：Blue;"> On  错误  GoTo  Command2_Click_Err 
 
  Dim  rs  As  DAO.Recordset 
  Dim  sqlStmt  As   String  
 
 
  On  错误  GoTo  Command2_Click_Err '报告查询代码的错误 
 
 DoCmd.OpenQuery  " DeleteExecupayTable" ，acViewNormal，acEdit 
 DoCmd.OpenQuery " 5_ExcepToExcupay" ，acViewNormal，acEdit 
 DoCmd.OpenQuery " 7_Su mToExecupay" ，acViewNormal，acEdit 
 DoCmd.OpenTable " 6_1_Execupay" ，acViewNormal，acReadOnly 
 
 
  On  错误  GoTo  DateStampError '报告DateStamp代码的错误 
 sqlStmt = "SELECT fldDate FROM [tblDateStamp]" ;  
 设置 rs = CurrentDb（）。OpenRecordset（sqlStmt）
 
 使用 rs 
 如果 .RecordCount = 0 那么 
 .AddNew '在添加记录之前首次使用 
 .Fields（" ; fldDate" ）=  Date  
 .Update 
  Else  
 .MoveFirst 
。编辑
 .Fields（" fldDate" ）= 日期 
 .Update 
  End  如果 
 结束 使用 
 rs.Close 
  Set  rs =  Nothing  
 
 
 Command2_Click_Exit：
 退出  Sub  
 
 Command2_Click_Err：
 MsgBox "打开查询代码失败。 " & 错误 $ 
 恢复 Command2_Click_Exit 
 
 DateStampError：
 MsgBox "DateStamp代码失败。 " & 错误 $ 
 恢复 Command2_Click_Exit 
 
 
 结束  Sub  
 

解决方案

要根据前一个表单的输入更改表单的内容或外观，至少有两种方法可以完全根据代码执行此操作：

1. 
   

   如果第一个表单仍然打开，您可以使用全局Forms集合直接引用第一个表单上的控件Controls属性。例如：

   
   

   Form1上的按钮只打开Form2：

   
   

      Private Sub  btnOpenForm2_Click（）
    DoCmd.OpenForm " Form2" ，acNormal 
     End Sub   

I have two forms in my vba app. What I'm attempting to do is a few things. When a user clicks on the last button on my Primary form, they are shown another form that will ask them to select values from an option group and then submit when they have selected that value. From there what I'm having issues with are

1. I'd like to post the data from the Primary form to a sql database (which I'm not sure how to do)

2. Put a timestamp on the data when it's submitted to the database (which I think I have the code for) and

3. When the user presses the submit button on the secondary form, that the same database is appended with the values from that form (either 1, 2, or 3) (which I don't know how to do either)

Here is the code I have for my primary form:

Option Compare Database

'------------------------------------------------------------
' Command0_Click
'
'------------------------------------------------------------
Private Sub Command0_Click()
On Error GoTo Command0_Click_Err

  DoCmd.OpenQuery "1_LogInScratchPad", acViewNormal, acEdit
  DoCmd.OpenTable "2_ScratchPad", acViewNormal, acEdit


Command0_Click_Exit:
  Exit Sub

Command0_Click_Err:
  MsgBox Error$
  Resume Command0_Click_Exit

End Sub

'------------------------------------------------------------
' Command1_Click
'
'------------------------------------------------------------
Private Sub Command1_Click()
On Error GoTo Command1_Click_Err


  DoCmd.OpenQuery "2_ExceptionsScratchPad", acViewNormal, acEdit
  DoCmd.OpenTable "3_ExcepScratchPad", acViewNormal, acEdit


Command1_Click_Exit:
  Exit Sub

Command1_Click_Err:
  MsgBox Error$
  Resume Command1_Click_Exit

End Sub

'------------------------------------------------------------
' Command2_Click
'
'------------------------------------------------------------
Private Sub Command2_Click()
DoCmd.OpenForm "Form1", acNormal, acEdit
On Error GoTo Command2_Click_Err

Dim rs As DAO.Recordset
Dim sqlStmt As String


On Error GoTo Command2_Click_Err  'Error reporting on query code

  DoCmd.OpenQuery "DeleteExecupayTable", acViewNormal, acEdit
  DoCmd.OpenQuery "5_ExcepToExcupay", acViewNormal, acEdit
  DoCmd.OpenQuery "7_SumToExecupay", acViewNormal, acEdit
  DoCmd.OpenTable "6_1_Execupay", acViewNormal, acReadOnly
  
  
On Error GoTo DateStampError    'Error reporting on DateStamp code
sqlStmt = "SELECT fldDate FROM [tblDateStamp]"
Set rs = CurrentDb().OpenRecordset(sqlStmt)

With rs
  If .RecordCount = 0 Then
    .AddNew 'For first time use before a record added
    .Fields("fldDate") = Date
    .Update
  Else
    .MoveFirst
    .Edit
    .Fields("fldDate") = Date
    .Update
  End If
End With
rs.Close
Set rs = Nothing


Command2_Click_Exit:
  Exit Sub

Command2_Click_Err:
 MsgBox "Open Query code failed. " & Error$
 Resume Command2_Click_Exit

DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Command2_Click_Exit


End Sub

解决方案

To change the contents or appearance of a form based on input from a previous form, there are at least 2 ways you could do this purely from code:

1. If the first form is still open, you can directly reference the control on the first form using the global Forms collection and the Controls property. For example:

   The button on Form1 just opens Form2:

   Private Sub btnOpenForm2_Click()
    DoCmd.OpenForm "Form2", acNormal
   End Sub

这篇关于将数据提交到sql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！