Some time ago, the lovely NeoPa helped me out with some complicated code. Now I need to make a slight change in our design.
The code does this:
You enter a 5 digit customer number (CUST_NUM) and press ENTER, enter a 3 or 4 digit box number (BOX_NUM) and press ENTER, and it assigns the box number to that customer number and links it to their most recent order number (ORDER_NUM). Additionally, it captures "todays" date (DATE_BOX_SHIP) which later updates another table's field (DATE_SHIP).
(OK,you can see why NeoPa had to write the code!) :-)
What I want to do is simply BE ABLE to overwrite the DATE_BOX_SHIP field it captures automatically. Usually I take care of this in the Properties box but I've been unsuccessful with that. I'm thinking something in my code is telling this field "Don't let anyone type there." This form is working BEAUTIFULLY and I don't want to compromise anything, so I'll just ask, can this be done, and still work the same?
Thanks in advance! - Option Explicit
-
Option Compare Database
-
-
Public strLastScan As String
-
Public db As DAO.Database
-
-
Private Sub Form_Open(Cancel As Integer)
-
Set db = CurrentDb
-
End Sub
-
-
Private Sub txtScanCapture_AfterUpdate()
-
Dim strSQL As String
-
-
Select Case Len(Me.txtScanCapture)
-
Case 3, 4
-
'Box
-
If strLastScan <> "Customer" Then
-
MsgBox "A customer ID must be scanned first before scanning boxes."
-
Else
-
'Is box registered in database?
-
If DCount("BOX_NUM", _
-
"tblBOX", _
-
"BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
-
'Box does not exist in DB
-
MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
-
Else
-
Me.txtScan_Box_Num = Me.txtScanCapture
-
'Box exists.
-
'Assign box to current customer, set shipping date=now, and received date to null
-
strSQL = "UPDATE tblBOX " & _
-
"SET [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
-
", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
-
", [DATE_BOX_SHIP]=Date()" & _
-
", [DATE_BOX_RETURN]=Null " & _
-
"WHERE ([BOX_NUM]='" & Me.txtScanCapture & "')"
-
DoCmd.SetWarnings (False)
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings (True)
-
Me.subfrmBOX_SHIPPING.Requery
-
'Update the DATE_SHIP in tblOrders where necessary
-
With db.OpenRecordset("tblORDERS", dbOpenDynaset)
-
Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
-
If Not .NoMatch Then
-
If IsNull(![DATE_SHIP]) Then
-
Call .Edit
-
![DATE_SHIP] = Date
-
Call .Update
-
End If
-
End If
-
Call .Close
-
End With
-
End If
-
strLastScan = "Box"
-
Me.tb_Scan_Cust_Num.BackStyle = 1
-
Me.txtScan_Box_Num.BackStyle = 0
-
End If
-
-
Case 5
-
'Customer
-
'Lets find customer entered
-
strSQL = "SELECT [CUST_NUM]" & _
-
", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
-
"FROM tblORDERS " & _
-
"WHERE [CUST_NUM]='" & Me.txtScanCapture & "'" & _
-
"GROUP BY [CUST_NUM]"
-
With db.OpenRecordset(strSQL, dbOpenSnapshot)
-
If .RecordCount = 0 Then
-
MsgBox "Customer number not recognized"
-
'Do whatever you want to handle this case
-
Else
-
strLastScan = "Customer"
-
Me.tb_Scan_Cust_Num.BackStyle = 1
-
Me.txtScan_Box_Num.BackStyle = 1
-
Me.tb_Scan_Cust_Num = !CUST_NUM
-
Me.Max_ORDER_NUM = !MaxOfORDER_NUM
-
End If
-
Call .Close
-
End With
-
-
Case Else
-
'Some sort of error or user error
-
MsgBox "Input error, resetting"
-
End Select
-
-
Me.txtScanCapture = ""
-
End Sub
-
3 1716
The shipment date of the box is set in line 33 of the code posted, and the shipment date of the order is set in line 46 (if the date was empty).
Now there is nothing in the code that should prevent you from setting the date, after the box has been scanned.
If the textbox is preventing you from clicking on it, its because it has its ENABLEDproperty set to False or because the form has its enabled property set to false.
If you can click the textbox and move the cursor while in the textbox, but not update it, its because either the textbox has its LOCKED property set to false, or because the forms ALLOWEDITS property is set to false.
Ps. I wrote the code or atleast the first version of it. :P
Thanks Smiley! Indeed I did have the FORM properties incorrect. I had previously only checked the field properties. Never even thought about checking the form. So I solved the "clicking in it" problem. However, I'm still stuck on the update problem. I can click in it, but not overwrite. And I think you meant "LOCKED property set to TRUE" above, but I tried it both ways anyway.
Here are my current settings: FORM: Enabled Yes, Locked No, Allow Edits Yes, Data Entry tried Yes&No.
TEXTBOX/FIELD (not sure of correct terminology) Enabled YES, Locked No.
Any other ideas?
PS. I think I found another work around to reach my goal where I can just leave this alone, so if it's too much of a headache I'll just forget about it. However, it might be a good learning experience to try to figure it out.
PS. I do recall you writing that code now. NeoPa took it and made some changes foe me. I'm sorry I forgot about your good work!!!! :-) Glad to see you still on here helping poor people like me. LOL.
You are correet about the Locked TRUE/False mixup I made.
The form should be:
ENABLED=True
Locked=False
AllowEdits=True
DataEntry=False
The control (textbox) should be:
Enabled=True
Locked=False
If you can now "enter" the field and move the cursor around, it means its no longer disabled. If you still can't edit it, it might be related to the underlying query for the subform. Some forms of queries are not updatable for instance. If you have checked the above properties, the next step would be to check the forms recordsource, and after that the control's controlsource.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Michael |
last post by:
I know how to display random jokes or sayings. But only if I reload
the page does the script select a new saying. How can I click on the
saying and have it load a new one in its place.
|
by: Matt |
last post by:
Can we click a link and it will submit the form to the other page?
Usually we will submit the
form to other page by clicking a submit button.
Now I need to do the following, but what if I have...
|
by: David |
last post by:
Ok. Can PHP control a form value directly? Eg. like the javascript
"document.Form1.text01.value = "Y"". Eg. allow me to put a value into
a form text field on page load. Either I missed this in...
|
by: steve bull |
last post by:
I would like my application to display the main form to cover the whole screen.
At present I do this by calling
Screen screen = Screen.PrimaryScreen;
and setting the form size to this. The...
|
by: NSF12345 |
last post by:
I have made a small application, and I have an invisible, or slightly transparent, form so that you can see through it at whats underneath. However, i would also like to make it a CLICK TROUGH form...
|
by: Franky |
last post by:
I have a MDI form open and two child forms open in it.
Each of the children contains a usercontrol which contains a richtextbox.
The topmost form's caption bar is highlighted but I click on the...
|
by: Deano |
last post by:
I'm in the situation of having to print a form - not a report. This will
fit nicely in a landscape format but by default it prints portrait. Is
there any code to make it play nice?
|
by: Amanduh |
last post by:
Okay, this is kind of a weird question that probably has an obvious answer. I'm making a form that allows me to enter new visits for a study that has multiple visits. For example, Study1 has...
|
by: mrhaddad |
last post by:
Hi
First post on this board. There is an error on my portfolio site that I really can't seem to figure out. I have a Contact Flex App in an IFRAME in the Contact DIV that fades in where you...
|
by: kcdoell |
last post by:
Hello:
I have the following afterupdate event:
Private Sub GWP_AfterUpdate()
'Updates the Total calculation in the control "SumGWP" on the quick reference
'table that is located on the form...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |