Access SQL语句从表中的组合框输入错误的值-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

Access SQL语句从表中的组合框输入错误的值

保持可爱mmm 2019-11-18 11:52:00 74

我正在尝试构建一个Access数据库,该数据库允许我的工作站点为我作为保姆的客户分类事件类型信息。

主表中的每个条目t_OutingDetails都包含这些事件的常规信息和特定于事件类型的信息。常规信息包括地点名称,地址和电话号码,特定于事件的信息包括类型,季节的开始和结束时间以及事件的费用。特定于事件的字段链接到查找表。事件类型,季节开始和结束以及事件成本的查找表每个都有ID作为第1列,详细信息字符串作为第2列。在行源中,我使用查询对空条目进行排序(并按字母顺序对查找表记录进行排序对于事件类型字符串);绑定列为0,列数为1。在表中,链接字段的绑定列为1。下面,我

在设置这两种形式时,我在使用组合框向每个条目中的字段分配特定于事件的信息时遇到了一个问题。在任何一种情况下,我检查表时都会得到错误/不稳定的结果。该值通常会从我在组合框中选择的值上移1(有时甚至更高),但我不知道为什么。最初,我在查询生成器中手动执行查询或使用VBA后,它就可以工作。在这两种情况下,首次使用后都会恢复为该故障。

我尝试了几种方法来解决此问题,但没有解决方法:

1)无论是链接查找表还是事件类型的空排序查询,我都得到相同的结果。

2)我在VBA中尝试了2件事来解决此问题,结果相同。首先,我更改了VBA语句以直接引用组合框的值。其次,我知道combobox索引从0开始而不是1,因此我尝试在VBA中将数字向前移动1,这引起了其他问题。

3)我修复了数据库。

4)我将组合框的“绑定列”更改为1而不是0。(这与表字段会产生很大的不同吗?)

看起来“ Access ComboBox保存值 ”,“ Access组合框值 ”和“ Access 2007 Combobox显示表中的错误结果 ”在某种程度上是相同的问题。我有事吗

这是用于将信息添加为新条目的代码:

Private Sub btn_AddOuting_Click() Dim strSQL As String Dim eventSlot As String, seasonStartSlot As String, seasonEndSlot As String, moneyReqSlot As String, prevSlot As String Dim eventDetails(5, 4) As Integer '5 rows of event slots with 4 fields (event type, season start, season end, and money required) Dim slotNo As Integer, eventNo As Integer Dim boolAnyBlankReqFields As Boolean

strSQL = ""

For slotNo = 5 To 1 Step -1 eventSlot = "cmb_Event" & slotNo seasonStartSlot = "cmb_StartOfSeason" & slotNo seasonEndSlot = "cmb_EndOfSeason" & slotNo moneyReqSlot = "cmb_MoneyRequired" & slotNo prevSlot = "cmb_Event" & (slotNo - 1)

  If eventSlot = "cmb_Event1" Then
     If (Forms("f_AddOuting").Controls(eventSlot) = 0 Or Forms("f_AddOuting").Controls(seasonStartSlot) = 0 Or _
         Forms("f_AddOuting").Controls(seasonEndSlot) = 0 Or Forms("f_AddOuting").Controls(moneyReqSlot) = 0) Then
        boolAnyBlankReqFields = True
        Me.lbl_Required.Visible = True
        If (Forms("f_AddOuting").Controls(eventSlot) = 0) Then
           Forms("f_AddOuting").Controls(eventSlot).BorderColor = RGB(255, 85, 85)
        End If
        If (Forms("f_AddOuting").Controls(seasonStartSlot) = 0) Then
           Forms("f_AddOuting").Controls(seasonStartSlot).BorderColor = RGB(255, 85, 85)
        End If
        If (Forms("f_AddOuting").Controls(seasonEndSlot) = 0) Then
           Forms("f_AddOuting").Controls(seasonEndSlot).BorderColor = RGB(255, 85, 85)
        End If
        If (Forms("f_AddOuting").Controls(moneyReqSlot) = 0) Then
           Forms("f_AddOuting").Controls(moneyReqSlot).BorderColor = RGB(255, 85, 85)
        End If
     Else
        eventDetails(1, 1) = Forms("f_AddOuting").Controls(eventSlot) + 1
        Forms("f_AddOuting").Controls(eventSlot).BorderColor = RGB(0, 0, 0)
        eventDetails(1, 2) = Forms("f_AddOuting").Controls(seasonStartSlot) + 1
        Forms("f_AddOuting").Controls(seasonStartSlot).BorderColor = RGB(0, 0, 0)
        eventDetails(1, 3) = Forms("f_AddOuting").Controls(seasonEndSlot) + 1
        Forms("f_AddOuting").Controls(seasonEndSlot).BorderColor = RGB(0, 0, 0)
        eventDetails(1, 4) = Forms("f_AddOuting").Controls(moneyReqSlot) + 1
        Forms("f_AddOuting").Controls(moneyReqSlot).BorderColor = RGB(0, 0, 0)
     End If
  Else
     If (Forms("f_AddOuting").Controls(eventSlot).Enabled = False) Then
        eventDetails(slotNo, 1) = 1
        eventDetails(slotNo, 2) = 1
        eventDetails(slotNo, 3) = 1
        eventDetails(slotNo, 4) = 1
     Else
        eventDetails(slotNo, 1) = Forms("f_AddOuting").Controls(eventSlot)
        eventDetails(slotNo, 2) = Forms("f_AddOuting").Controls(seasonStartSlot)
        eventDetails(slotNo, 3) = Forms("f_AddOuting").Controls(seasonEndSlot)
        eventDetails(slotNo, 4) = Forms("f_AddOuting").Controls(moneyReqSlot)
     End If
  End If

Next

If boolAnyBlankReqFields = True Then MsgBox ("Fill in required information.") Else strSQL = "INSERT INTO t_OutingDetails (Event1, SeasonStart1, SeasonEnd1, MoneyRequired1, " _ & "Event2, SeasonStart2, SeasonEnd2, MoneyRequired2, Event3, SeasonStart3, SeasonEnd3, MoneyRequired3, " _ & "Event4, SeasonStart4, SeasonEnd4, MoneyRequired4, Event5, SeasonStart5, SeasonEnd5, MoneyRequired5) VALUES ('" _ & eventDetails(1, 1) & ", " & eventDetails(1, 2) & ", " & eventDetails(1, 3) & ", " & eventDetails(1, 4) & ", " _ & eventDetails(2, 1) & ", " & eventDetails(2, 2) & ", " & eventDetails(2, 3) & ", " & eventDetails(2, 4) & ", " _ & eventDetails(3, 1) & ", " & eventDetails(3, 2) & ", " & eventDetails(3, 3) & ", " & eventDetails(3, 4) & ", " _ & eventDetails(4, 1) & ", " & eventDetails(4, 2) & ", " & eventDetails(4, 3) & ", " & eventDetails(3, 4) & ", " _ & eventDetails(5, 1) & ", " & eventDetails(5, 2) & ", " & eventDetails(5, 3) & ", " & eventDetails(5, 4) & ");"

  DoCmd.RunSQL strSQL

  MsgBox ("Entry submitted successfully.")

  boolAnyBlankReqFields = False

End If

btn_AddOuting_Click_Exit: Exit Sub btn_AddOuting_Click_Err: MsgBox Err & ": " & Err.Description Resume btn_AddOuting_Click_Exit End Sub 这是更新事件类型的代码:

Private Sub btn_NewEvent1_Click() Dim strSQL As String

If Me.cbo_NewEvent1 <> 0 Then strSQL = "UPDATE t_OutingDetails SET [t_OutingDetails].[Event1] = [Forms]![f_UpdateOutings]![cbo_NewEvent1] WHERE (((t_OutingDetails.ID)=[Forms]![f_UpdateOutings]![cbo_PlaceName]));" DoCmd.RunSQL strSQL Me.txt_Event1.Requery Me.cbo_NewEvent1 = 0 Else MsgBox ("No selection was made for the first event type.") End If End Sub

SQL 数据库 索引
分享到
取消 提交回答
全部回答(0)
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程