Object Change to Open Report w Correct Value

[Jenny at APOC]

I have been stumbling for too long over this one. I have a form with a Text
box and the value placed in that Text box (LotNumberTextBox) needs to be
transferred to the Report, which uses a query based on the value in the text
box. This means that I need the value to stay there and open the report,
then delete the value so the user will have an empty text box when they go
back to the form. Here is the code:

Private Sub LotNumberTextBox_Change()

'LotNumberTextBox.SetFocus
'ComponentList.Requery
Dim length$
Dim project$, box$
Dim stDocName As String

' waiting for textbox to have 16 characters
length$ = Len(CStr(LotNumberTextBox.Text))
If length$ < 16 Then
Exit Sub
End If
' end of waiting for textbox to have 16 characters


'check to see if lot number is exists in dbo_lnaLotNbrDetailALL
SQLStmt = "Select LotNbr from dbo_lnaLotNbrDetailALL as lotcheck
where LotNbr = '" + LotNumberTextBox.Text + "'"
TempDataTable.RowSource = SQLStmt
TempDataTable.Requery
Me!TempDataTable.Selected(0) = True
If IsNull(TempDataTable.Column(0, TempDataTable.ListIndex)) Then
If (MsgBox("Lot Number Does Not Exist", vbOKOnly, "Action
Aborted!") = vbOK) Then
End If
LotNumberTextBox.Value = Null
Exit Sub
End If
'end of checking to see if lot number is valid

'If lot exists in FileFolder table then we want to open the report for
location
SQLStmt = "SELECT LotFolder from FileFolder as lotcheck where
LotFolder = '" + LotNumberTextBox.Text + "'"
TempDataTable.RowSource = SQLStmt
TempDataTable.Requery
Me!TempDataTable.Selected(0) = True
If Not IsNull(TempDataTable.Column(0, TempDataTable.ListIndex))
Then
If (MsgBox("This File Folder has already been entered",
vbOKOnly, "File is already in a FC") = vbOK) Then

'Open report
DoCmd.OpenReport ("FileFolderinCarton"), acViewPreview
End If
LotNumberTextBox.Value = ""
Exit Sub
End If

'Else we want to allow user to add to an available file carton (FC)
SQLStmt = "Select LotFamily from dbo_lnaLotNbrDetailALL where LotNbr =
'" + LotNumberTextBox.Text + "'"

ComponentList.RowSource = SQLStmt
ComponentList.Requery


Me!ComponentList.Selected(0) = True
ComponentList.Value = CStr(ComponentList.Column(0,
ComponentList.ListIndex))


AvailableFCartonList.Requery


End Sub

 

[Rob Oldfield]

What data is FileFolderinCarton based on? A query somewhere?

One other point... the idea of automatically triggering a report to open
when the user types the 16th character into a box is, in my opinion,
horrible. It's different from the general method that GUIs in general work.
Better to just allow the user to type whatever they want, sit back and check
they have it right, then click a button to tell the report to go (and check
the number of characters at that point)?

 

[Jenny at APOC]

FileFolderinCarton is based on table data. The same table that the check is
done on. I am not familiar with GUI. If you have another suggestion on how
to do a check I am more than ahppy to implement it. I just need to assure
that the user uses an existing value and I am checking in two different
tables. One SQL table and anouther Access table. Thanks.

 

[Rob Oldfield]

A GUI is a 'Graphical User Interface' - Windows, Mac, whatever. The point
is that there is nothing (that I'm aware of) in any current software where
an action is triggered by the pressing of the nth key in a string. Better
would be, as I tried to say before, let them type whatever they want... then
hit a button... and the first thing that that does is check the number of
characters... if it's not 16, stop the code and warn the user.

I really don't get what you're doing at the moment. You say you open a
report "which uses a query based on the value in the text box" - where's
that query? Is that FileFolderinCarton? Is FileFolderinCarton a query or a
table? If it's a query, then how are you trying to reference the text box
value? And why are you checking two tables? Are they linked? Why is one
SQL and one Access? Sorry for all of my questions, but your question isn't
making any sense at the moment.

I suppose my actual questions would be...

Where is your data?
What are the links betwen those tables?
What do you actually want to happen?

 

[Jenny at APOC]

The "LotNumber" is on two different tables. The first table (SQL) is a
production server and I am only using it to verify that the lot exists (not
allowed access so I read off of it). The second table is created in my
database and the LotNumber is linked between the SQL and the Access. There
is different information on the tables. The link is the LotNumber. (I am
sure this is confusing).

Anyway, what I want to happen is that sometimes the Report Opens with the
"LotNumberTextBox" value and sometimes it does not and I don't know why. The
lot is still on the form and I am directing the report to look at that value,
so why does it not always really read it? I tried what you said and am
rearranging the code, but am not successful yet. As you can see I have just
learned VB and still struggle with some basic concepts. I do appreciate your
input and thank you for your time. If you have any ideas let me know.

 

[Rob Oldfield]

I still don't see how the report FileFolderinCarton references the LotNumber
value. How does that work? And what do you mean by the "LotNumberTextBox"
value on the report? That's a text box on the report?

 

[Jenny at APOC]

In the FileFolderinCarton report I have a query that uses the value from the
form where the LotNumberTextBox is found. So on the report I am basically
transferring the value from a form to a report and then deleting it from the
form. Does that help?

 

[Rob Oldfield]

OK. It's beginning to make more sense now. One last question though: how
does the underlying query for the report reference the value in the text
box? It's just a saved query including a
forms![formname]![LotNumberTextBox] or some other way?

 

[Jenny at APOC]

Yes a report with a query that references the
[form]![formname]![lotnumbertextbox]. I think I got it though. I was doing
too many things at once. I changed the Report opening check to be performed
when the user double-clicks a different list box. That way with the change
function below I am only checking that the lot is valid. Then when the user
makes a decision I check to make sure the lot is not already in the db
table....It is working consistantly now. Thanks.

Jenny

OK. It's beginning to make more sense now. One last question though: how
does the underlying query for the report reference the value in the text
box? It's just a saved query including a
forms![formname]![LotNumberTextBox] or some other way?

In the FileFolderinCarton report I have a query that uses the value from the
form where the LotNumberTextBox is found. So on the report I am basically
transferring the value from a form to a report and then deleting it from the
form. Does that help?