Hi all,
The database I am making is essentially for data entry of files and which boxes they have been placed into for storage. I have multiple textboxes on my form but I am only focused on a couple. So previously, the user would have filled in information on the first form for information about the box. This includes a general location. Once validated, the LocationID is stored in a global variable.
The first form closes and then opens a second form where all of the files being listed belong to the box from the first form (all have the same BoxID). The global variable then populates a "hidden" bound textbox (txtLocationID) on the second form. Using a query, this then populates an unbound textbox (txtSearch) with the location of the box (the user will never see the LocationID, only there for structure).
So far so good. The problem lies when the user deletes the text in the unbound textbox to change the location. I can't work out how to change the bound textbox value to 0 if the user deletes the text in the unbound textbox. This is essential as currently it just keeps the same LocationID as previously pulled through so on later validation, it would appear that the file is from the originally stated location rather than not been entered.
I have tried putting the following combinations of code in the before update, after update, on change, on lost focus events of the unbound textbox. - If Me.txtSearch.Text = "" Then 'Have also omitted .Text
-
Me.txtLocationID.Value = 0 'Have also omitted .Value
-
End If
- If IsNull(Me.txtSearch.Text) Then 'Have also omitted .Text
-
Me.txtLocationID.Value = 0 'Have also omitted .Value
-
End If
All help will be greatly appreciated!
Typically, in a case like this, I put the code into the AfterUpdate event and structure it like: - If Len(Nz(Me.txtSearch.Value, "") = 0 Then
-
Me.txtLocationID.Value = 0
-
End If
I don't see any reason why Me.txtLocationID.Value wouldn't be updated. Have you validated that the value is not being changed?
It sounds like you have a fair amount of code being run on Open of the Form, some of which relies on the value of Me.txtLocationID.Value, maybe after you update the value of Me.txtLocationID.Value you need to call the same code that is executed on Open of the Form, or at least the portion that is run after the Me.txtLocationID.Value is updated.
7 5928
Typically, in a case like this, I put the code into the AfterUpdate event and structure it like: - If Len(Nz(Me.txtSearch.Value, "") = 0 Then
-
Me.txtLocationID.Value = 0
-
End If
I don't see any reason why Me.txtLocationID.Value wouldn't be updated. Have you validated that the value is not being changed?
It sounds like you have a fair amount of code being run on Open of the Form, some of which relies on the value of Me.txtLocationID.Value, maybe after you update the value of Me.txtLocationID.Value you need to call the same code that is executed on Open of the Form, or at least the portion that is run after the Me.txtLocationID.Value is updated.
Well that works perfectly as usual jforbes!
Is there any reason why the Len(Nz... function would work instead of the IsNull and = ""?
It sounds like might have tried the proper combination, but I don't know for sure as you don't say what things you tried together. This should work in txtSearch's After Update event: - If Me.txtSearch & "" = "" Then
-
Me.txtLocationID = 0
-
End If
If this does not work, then make sure that you have OPTION EXPLICIT at the top of your form's code and then try compiling your program (Debug>Compile). You can also put a break point on the first line of your code and then run it. Code execution will stop on that line before it is executed and then you can use the immediate window to test what the values are for those controls.
Edit** I didn't see the other posts before I posted this. You can still use the debugging tips to see why your original code didn't work.
The value of a TextBox or Field can be a Null or a Zero length string, which evaluate differently and a Null does not equal a Empty string. Both of the ways Seth and I build the expression force the left side of the expression to be a String and then evaluate from there.
Back again with a query related to this stuff.
It's going to be complicated to explain but I'll give it a go and hope you guys understand what I mean.
So with this same data entry form, when the user enters into the textbox a location that is not already in my tblLocation, a dialog box opens up giving the user the option to add the new location to the table or to select an existing location from a list box. Once the user does either of these options, I then need the txtLocationID textbox to update to the new LocationID of the new/existing location and also need the unbound textbox to update to the new/existing location. Unfortunately, I can't get this to work... hopefully that's where you guys can help me!
The first function below is what I am using to determine whether or not the entered location already exists in the table. If it does, it updates the txtLocationID box (this all works fine. It it doesn't already exist, the new dialog box pops up. This also works fine for now. -
Private Function fQryGetLocationID(Optional Cancel As Integer)
-
Dim db As Database
-
Dim qdf As QueryDef
-
Dim rs As DAO.Recordset
-
Dim intX As Integer
-
-
Set db = CurrentDb()
-
Set qdf = db.QueryDefs("qryUpdateLocationID")
-
qdf.Parameters("test") = Me.txtLocation
-
Set rs = qdf.OpenRecordset()
-
-
If rs.RecordCount <> 0 Then
-
With rs
-
.MoveFirst
-
Me.txtLocationID.Value = .Fields("LocationID")
-
End With
-
Else
-
MsgBox "No matching location!"
-
glTestNoLocation = Me.txtLocation.Text
-
Cancel = True
-
DoCmd.OpenForm "frmNoLocation", , , , acFormAdd, acDialog
-
End If
-
rs.Close
-
db.Close
-
Set rs = Nothing
-
Set qdf = Nothing
-
Set db = Nothing
-
End Function
-
The popup box has a textbox called txtUnsavedLoc, a save button for the new location called cmdSave, a list box populated with similar locations to the originally entered location and an ok button to select an option from the list box.
On loading, the list box is populated using a query where the unbound text box from the data entry form is tested using a Levenshtein function and produces locations with <5 differences. -
Private Sub Form_Load()
-
-
txtUnsavedLoc = glTestNoLocation
-
-
Dim db As Database
-
Dim qdf As QueryDef
-
Dim rs As DAO.Recordset
-
Dim intX As Integer
-
-
Set db = CurrentDb()
-
Set qdf = db.QueryDefs("qryLevLocation")
-
Me.txtUnsavedLoc.SetFocus
-
qdf.Parameters("LevLocation") = glTestNoLocation
-
Set rs = qdf.OpenRecordset()
-
-
'remove all items in the list box before querying to add them
-
With rs
-
With Me.lstResults
-
For intX = .ListCount - 1 To 0 Step -1
-
Call .RemoveItem(intX)
-
Next intX
-
End With
-
End With
-
Me.lstResults.Requery
-
-
If rs.RecordCount <> 0 Then
-
With rs
-
.MoveFirst
-
While Not .EOF
-
Me.lstResults.AddItem .Fields("LocationID") & ";" & .Fields("FileLocation")
-
.MoveNext
-
Wend
-
End With
-
End If
-
-
rs.Close
-
db.Close
-
Set rs = Nothing
-
Set db = Nothing
-
-
End Sub
-
If the user selects an option from this box and then presses the Ok button, I need the selected option to populate the unbound textbox with the text and the bound textbox with the locationID. I have used the following. It replaces the unbound textbox but the bound textbox doesn't change. -
Private Sub cmdOK_Click()
-
-
If Me.lstResults.ListCount <> 0 Then
-
If Me.lstResults.ListIndex <> -1 Then
-
If CurrentProject.AllForms("frmAddFile").IsLoaded = True Then
-
[Forms]![frmAddFile]![txtLocationID] = Me.lstResults.Column(0)
-
[Forms]![frmAddFile]![txtLocation] = Me.lstResults.Column(1)
-
End If
-
DoCmd.Close acForm, "frmNoLocation"
-
End If
-
End If
-
End Sub
-
Any ideas where I'm going wrong?
Per site rules, you can only have one question per thread, so please post this question to a new thread. You can provide a link to this thread if you think that it would help the question.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Paul .V. |
last post by:
I have a report which I call to print from a form. For security
reasons I have created a global variable:
Const Company = "This Test Company"
I call that constant whereever I want the company...
|
by: jaYPee |
last post by:
I have an unbound textbox in my report that is bound from the textbox
in my Dialog Form using a controlsource using Access 97. However after
I ported my database to SQL Server 2000 and use Access...
|
by: BerkshireGuy |
last post by:
Hello,
I have a query that references abn unbound textbox on a form to get the
criteria. The unbound textbox can read 'Brian' or 'Dave'. Or just
'Brian'.
However, I think the query is seeing...
|
by: TD |
last post by:
How can I trap this and keep it from happening?
|
by: colleen1980 |
last post by:
Hi: There are two textboxs in my main form. One is bound and another is
unbound. There is no entry in the unbound textbox as values come into
automatically after entering some information in the...
|
by: Richard |
last post by:
How is the result of query placed in a unbound textbox ?
Suppose
CriteriaLookups has columns TableName, KeyColumn, KeyValue,
DataColumn
Foo,x,11,xhat
Bar,z,3,xyzzy
And
|
by: Enrique2 |
last post by:
Hi experts,
I was wondering if you could help me out here.
AccumulationSum is an unbound textbox that calculates the sum of bound field Accumulation. The control source is =Sum(
Whe I try...
|
by: crazyhouse |
last post by:
I want to use temporary information from an unbound textbox, but i cant seem to run any code on an unbound textbox. Is this possible. I have it working with a bound "control" but cant seem to make...
|
by: bre1603 |
last post by:
I'm trying to run an UPDATE statement on a table that changes every time the user runs the code.
Here's some info:
(On a form) The user imports a text file, using a button to open a file browser...
|
by: gcoaster |
last post by:
Hello *.*
Can someone please help, after hours and hours I am turning for help..
I have 4 fields that are unbound on a form.
img1 img2 img3 img4
When these are entered they are all...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |