使用表格通过查询显示表格字段 [英] Display table field through query using form

问题描述

只是一些背景资料。我的表格（HireHistory）有50列（水平）。我有一个Form（HireHistoryForm），它有两个文本框（HistoryMovieID和HistoryCustomerID）和一个按钮（该按钮运行查询'HireHistoryQuery'）

下面摘录了我的数据（CustomerID位于顶端）：

所以我需要的是，如果一个数字被输入到HistoryCustomerID框中，那么它会显示该列。例如如果输入的值是'1'，那么在我的查询中它会显示第1列的所有记录。

如果一个数字输入到HistoryMovieID框中（例如0001）那么它将显示该特定CustomerID的MovieID的所有实例。即在第1栏是ID，因此对于ID = 1它将显示0001在19/05/2006，然后将继续找到下一个0001等。

对于HistoryCustomerID，我试图把它放到我的'Field'中进行查询：

  = [Forms] ！[HireHistoryForm]！[HistoryCustomerID] 
  

但没有奏效。我的查询刚刚返回了一个标记为'10'的列，行仅由'10'组成。

如果你能帮忙，我会非常感激。 ：）

可怕的 结构化数据的方式。您真的需要像这样重组它：

  CustomerID MovieID HireDate 
 --- ------- ------- -------- 
 1 0001 19/05/2006 
 1 0003 20/10/2003 
 1 0007 13/08/2003 
 ... 
 2 0035 16/08/2012 
 2 0057 06/10/2012 
 ... 
 
 
 
 
你会生气的，并且
    
这个问题几乎不可能出现在任何地方。 
 
 
 
 
 
 $ b 
您修改过的数据结构是  非常轻微的改进，但它仍然对你不利。考虑到你的另一个问题这里你基本上要求一种方式当您执行查询时，动态修复您的数据结构。
 
 
好消息是您可以运行一次VBA代码一次将您的数据结构转换为可行的东西。开始创建你的新表，我将其称为HireHistoryV2
 
 
  ID  - 自动编号，主键
 CustomerID - 数字（长整数），索引（重复确定）
 MovieID  - 文本（4），索引（重复确定）
 HireDate  - 日期/时间，索引（重复确定）
  
将数据复制到新表的VBA代码如下所示：
 函数RestructureHistory（）
 Dim cdb作为DAO.Database，rstIn作为DAO.Recordset，rstOut作为DAO.Recordset 
 Dim fld作为DAO.Field， a（）As String 
 
 Set cdb = CurrentDb 
 Set rstIn = cdb.OpenRecordset（HireHistory，dbOpenTable）
 Set rstOut = cdb.OpenRecordset（HireHistoryV2，dbOpenTable ）
 
虽然不是rstIn.EOF 
对于每个fld在rstIn.Fields 
如果fld.Name像Hire *然后
如果不是IsNull（fld。 Value）然后
a = Split（fld.Value，on，-1，vbBinaryCompare）
 rstOut.AddNew 
 rstOut！CustomerID = rstIn！CustomerID 
 rstOut！MovieID = a（0）
 rstOut！HireDate = CDate（a（1））
 rstOut.Update 
 End If 
 End If 
 Next 
 Set fld = Nothing 
 rstIn.MoveNext 
 Loop 
 rstOut.Close 
 Set rstOut = Nothing 
 rstIn.Close 
 Set rstIn = Nothing 
 Set cdb = Nothing 
 MsgBoxDone！ 
 End Function 
  
 注意：您好像使用了dd / mm / yyyy日期格式，所以 仔细检查日期转换 以确保它们正确转换。
 
Just some background information. My table, (HireHistory) has 50 columns in it (horizontal). I have a Form (HireHistoryForm) which has a 2 text boxes (HistoryMovieID and HistoryCustomerID) and a button (the button runs the query 'HireHistoryQuery')

Here's an excerpt of my data (the CustomerID's are along the top): 


So what I need is so that if a number is entered into the HistoryCustomerID box, then it displays that column. e.g. if the value entered is '1', then in my query it will show all records from column 1. 

If a number is entered into the HistoryMovieID box (e.g. 0001) then it displays all instances of that MovieID for the specific CustomerID's. i.e. In column 1 is the ID's, so for ID=1 it will show "0001 on 19/05/2006" then will go on to find the next instance of '0001' etc.

For the HistoryCustomerID I tried to put this into my 'Field' for the query: 
=[Forms]![HireHistoryForm]![HistoryCustomerID]
But it didn't work. My query just returned a column labelled '10' and the rows were just made up of '10'. 

If you could help I'd greatly appreciate it. :)
解决方案
No offense intended (or as little as possible, anyway), but that is a horrible way to structure your data. You really need to restructure it like this:
CustomerID  MovieID HireDate
----------  ------- --------
1           0001    19/05/2006
1           0003    20/10/2003  
1           0007    13/08/2003
...     
2           0035    16/08/2012
2           0057    06/10/2012
...
If you keep your current data structure then

You'll go mad, and
It's extremely unlikely that anyone else will go anywhere near this problem.


Edit

Your revised data structure is a very slight improvement, but it still works against you. Consider that in your other question here you are essentially asking for a way to "fix" your data structure "on the fly" when you do a query.

The good news is that you can run a bit of VBA code once to convert your data structure to something workable. Start by creating your new table, which I'll call "HireHistoryV2"
ID         - AutoNumber, Primary Key
CustomerID - Number(Long Integer), Indexed (duplicates OK)
MovieID    - Text(4), Indexed (duplicates OK)
HireDate   - Date/Time, Indexed (duplicates OK)
The VBA code to copy your data to the new table would look something like this:
Function RestructureHistory()
Dim cdb As DAO.Database, rstIn As DAO.Recordset, rstOut As DAO.Recordset
Dim fld As DAO.Field, a() As String

Set cdb = CurrentDb
Set rstIn = cdb.OpenRecordset("HireHistory", dbOpenTable)
Set rstOut = cdb.OpenRecordset("HireHistoryV2", dbOpenTable)

Do While Not rstIn.EOF
    For Each fld In rstIn.Fields
        If fld.Name Like "Hire*" Then
            If Not IsNull(fld.Value) Then
                a = Split(fld.Value, " on ", -1, vbBinaryCompare)
                rstOut.AddNew
                rstOut!CustomerID = rstIn!CustomerID
                rstOut!MovieID = a(0)
                rstOut!HireDate = CDate(a(1))
                rstOut.Update
            End If
        End If
    Next
    Set fld = Nothing
    rstIn.MoveNext
Loop
rstOut.Close
Set rstOut = Nothing
rstIn.Close
Set rstIn = Nothing
Set cdb = Nothing
MsgBox "Done!"
End Function
Note: You appear to be using dd/mm/yyyy date formatting, so check the date conversions carefully to make sure that they converted properly.

                        
这篇关于使用表格通过查询显示表格字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！