Ok. I'm trying to use VB code to determine if the value in an unbounded combo box on a form would create a new record if it were added to a table.
Background: I have a table to track orders through our warehouse. The user must begin tracking the order and then update it as it moves through the various stations in the warehouse. At the moment, i have two forms: one to BEGIN tracking an order and one to UPDATE the tracking. It is set up this way because I dont know how to check to see if the order being updated is already in the tracking table or if it needs to add a new record. This is a problem because the user has choose the right form to use based on if the order has already been tracked or not.
I'm relatively new to VB, but have used it in my database. I havent used the DCount function before. But i was thinking of something like this to solve my problem:
1) The user types the order number into an unbounded combo box in the update form. On the After Update event, code would run a DCount on the "Order_Number" key field of the Tracking Table. It would return either a "1" if the order has been tracked (i.e. already has a record in the table) or a "0" if the order has not been tracked.
2) That value (1 or 0) would then be added to a hidden text box on the form.
3) And "if/then" code would enable the different update buttons a user has access to based on whether the tracked value is 1 or 0.
The code as I've worked on it (without any kind of If/then statement) is
Dim Tracked As String
Dim OrderNum As String
OrderNum = Me.Order_Number
Tracked = DCount("Order_Number", "tbl_PT_Data_PickingTicketTracking", "Order_Number = '" & OrderNum)
Me.Hidden_Tracking_Result = Tracked
But it returns an error--Syntax error in string "order_number = '100' for example.
I dont know why this error is happening. Is this solution on the right track?
I'm sure it is clunky. But if i could figure out how to really use the DCount function to return a result, i think i could figure out ways to streamline it.
But i'm really just flying in the dark here.
Anyone have any ideas that would help me?
Background: I have a table to track orders through our warehouse. The user must begin tracking the order and then update it as it moves through the various stations in the warehouse. At the moment, i have two forms: one to BEGIN tracking an order and one to UPDATE the tracking. It is set up this way because I dont know how to check to see if the order being updated is already in the tracking table or if it needs to add a new record. This is a problem because the user has choose the right form to use based on if the order has already been tracked or not.
I'm relatively new to VB, but have used it in my database. I havent used the DCount function before. But i was thinking of something like this to solve my problem:
1) The user types the order number into an unbounded combo box in the update form. On the After Update event, code would run a DCount on the "Order_Number" key field of the Tracking Table. It would return either a "1" if the order has been tracked (i.e. already has a record in the table) or a "0" if the order has not been tracked.
2) That value (1 or 0) would then be added to a hidden text box on the form.
3) And "if/then" code would enable the different update buttons a user has access to based on whether the tracked value is 1 or 0.
The code as I've worked on it (without any kind of If/then statement) is
Dim Tracked As String
Dim OrderNum As String
OrderNum = Me.Order_Number
Tracked = DCount("Order_Number", "tbl_PT_Data_PickingTicketTracking", "Order_Number = '" & OrderNum)
Me.Hidden_Tracking_Result = Tracked
But it returns an error--Syntax error in string "order_number = '100' for example.
I dont know why this error is happening. Is this solution on the right track?
I'm sure it is clunky. But if i could figure out how to really use the DCount function to return a result, i think i could figure out ways to streamline it.
But i'm really just flying in the dark here.
Anyone have any ideas that would help me?
