T
Taiisien
Guest
First off i will start off with what i think would be the easier of the two issues i am having. I have created an options box and have three working options buttons, for the fourth button i would like to know if there is a way to get it to query more than one column of a table. I assume that there is a way to do this but i would need to know what type of divider i need between the column names in the coding in visual basic. here is the code i currently have where the word TENUMBER is the name of the first column i want to use...
Ok so that was the one that i think is actually possible this one i am not so sure. What i would like to do is to be able to update a certain field on two tables at the same time for the same item however the line that the item is on will change on one table but not the other. Basically this is for inventory of tooling in the shop i work for, when someone wants one of our tools they request it, we get it ready and move it to a ready for issue area calling it "staged". What i would like to do is have the form that i use to track what is "staged" also update the form that i use for my inventory. The problem i seem to be having is that tools are added and deleted from the staged inventory all the time while the actual "inventory" remains the same. So what i am trying to do is when i add a tool number to my staged form and mark it's status as staged i would like it to automatically update my inventory form stating that that same tool is no longer ready for issue but that it is staged. Is there a way that the inventory table (or form) can query the staged table (again or the form) and find the tool numbers that match the tool numbers from the inventory table and then change the status field on the inventory table? Wow that sounds much more complicated when i typed it out. I believe the main problem that i am having is that the staged form / table does not have the entire inventory in it and so the primary keys will not align ever.
Any help anyone can offer on either of these two items would be greatly appriciated though i must warn you that my working knowledge of access mostly comes from a kind of reverse engineering as i have needed things. Basically i have dug through the examples provided by microsoft and found items i like / needed then i looked at the code and played with it till i got my version to work. Now that is not true for everything but that is where i got the majority of my understanding. Again thanks for any help anyone can provide.
Code:
Private Sub cmdGo_Click()
' Requery the OutgoingResults form based on
' the selectioned items
On Error GoTo HandleErr
DoCmd.OpenForm "OutgoingResults"
With Forms!OutgoingResults
If Len(Me!cboSelect & "") > 0 Then
' Construct SQL for OutgoingData's Recordsource
Select Case optChoose
Case 1
' PHNUMBER
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " PHNUMBER Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 2
' NEEDDATE
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " NEEDDATE Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 3
' SSN
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " SSN Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 4
' TENUMBER
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " TENUMBER Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case Else
End Select
.RecordSource = mstrSQL
End If
End With
DoCmd.Close acForm, "FindOutgoing"
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindOutgoing.cmdGo_Click"
End Select
Resume ExitHere
Resume
End Sub
Private Sub opt4_GotFocus()
End Sub
Private Sub Command17_Click()
' Requery the PrintableOutgoing form based on
' the selectioned items
On Error GoTo HandleErr
DoCmd.OpenForm "PrintableOutgoing"
With Forms!PrintableOutgoing
If Len(Me!cboSelect & "") > 0 Then
' Construct SQL for OutgoingData's Recordsource
Select Case optChoose
Case 1
' PHNUMBER
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " PHNUMBER Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 2
' NEEDDATE
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " NEEDDATE Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 3
' SSN
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " SSN Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 4
' TENUMBER
mstrSQL = "SELECT * FROM OutgoingData Where " _
& " TENUMBER Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case Else
End Select
.RecordSource = mstrSQL
End If
End With
DoCmd.Close acForm, "FindOutgoing"
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindOutgoing.cmdGo_Click"
End Select
Resume ExitHere
Resume
End Sub
Private Sub optChoose_AfterUpdate()
' Populate rowsource of cboSelect
Dim strSQL As String
On Error GoTo HandleErr
Select Case optChoose
Case 1
' PHNUMBER
strSQL = "Select Distinct PHNUMBER from OutgoingData " _
& "Order By PHNUMBER"
Case 2
' NEEDDATE
strSQL = "Select Distinct NEEDDATE from OutgoingData " _
& "Order By NEEDDATE"
Case 3
' SSN
strSQL = "Select Distinct SSN from OutgoingData " _
& "Order By SSN"
Case 4
' TENUMBER
strSQL = "Select Distinct TENUMBER from OutgoingData " _
& "Order By TENUMBER"
Case Else
End Select
With Me!cboSelect
.Value = Null
.RowSource = strSQL
.Requery
.Value = .ItemData(0)
End With
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindOutgoing.optChoose_AfterUpdate"
End Select
Resume ExitHere
Resume
End SubOk so that was the one that i think is actually possible this one i am not so sure. What i would like to do is to be able to update a certain field on two tables at the same time for the same item however the line that the item is on will change on one table but not the other. Basically this is for inventory of tooling in the shop i work for, when someone wants one of our tools they request it, we get it ready and move it to a ready for issue area calling it "staged". What i would like to do is have the form that i use to track what is "staged" also update the form that i use for my inventory. The problem i seem to be having is that tools are added and deleted from the staged inventory all the time while the actual "inventory" remains the same. So what i am trying to do is when i add a tool number to my staged form and mark it's status as staged i would like it to automatically update my inventory form stating that that same tool is no longer ready for issue but that it is staged. Is there a way that the inventory table (or form) can query the staged table (again or the form) and find the tool numbers that match the tool numbers from the inventory table and then change the status field on the inventory table? Wow that sounds much more complicated when i typed it out. I believe the main problem that i am having is that the staged form / table does not have the entire inventory in it and so the primary keys will not align ever.
Any help anyone can offer on either of these two items would be greatly appriciated though i must warn you that my working knowledge of access mostly comes from a kind of reverse engineering as i have needed things. Basically i have dug through the examples provided by microsoft and found items i like / needed then i looked at the code and played with it till i got my version to work. Now that is not true for everything but that is where i got the majority of my understanding. Again thanks for any help anyone can provide.
