Opening a form inside a loop in Access VBA (1 Viewer)

G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
I have a large loop running, inside this loop, the program needs to stop for operator intervention. (See code below) Once the user and adjusted the data the loop continues on. At least that is how I need it to work. What is happening now is the program completes until the end of the file it is processing, and then stops. Does not stop for input for the records that need user interaction. What a, I missing??


code:
Code: 
        addrin = Mid(rs_datain!InputText, 15, 210)
        working = Trim(addrin)
        rs_code_5!postalcode = Mid(working, (Len(working) - 6), 7)
        working = Trim(Mid(working, 1, Len(working) - 8))
        rs_code_5!prov = Mid(working, (Len(working) - 1), 2)
        working = Trim(Mid(working, 1, Len(working) - 2))
        rs_code_5!city = Mid(working, (Int(Len(working) / 30) * 30 + 1), 255)
        rs_code_5!addr1 = Mid(working, 1, 30)
        rs_code_5!addr2 = Mid(working, 31, 30)
        rs_code_5!addr3 = Mid(working, 61, 30)
        rs_code_5!addr4 = Mid(working, 91, 30)
        rs_code_5!ref = Mid(rs_datain!InputText, 227, 30) '13
        rs_code_5!clientno = Val(Mid(rs_datain!InputText, 257, 8)) '14
        rs_code_5!created = Date
        rs_code_5!lastupdate = Date
        rs_code_5!active = True
        rs_code_5.Update
        DoCmd.OpenForm FormName:="CheckAddr", View:=acNormal, DataMode:=acFormEdit
 
Last edited by a moderator:
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
Try adding WindowMode:=acDialog to your OpenForm line.
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
theDBguy said:
Try adding WindowMode:=acDialog to your OpenForm line.
Click to expand...


Hey you are ACE! Worked like a charm. Got the window open, but all the fields (That are coming from the VBA code) are showing up as #Name? Not sure why that is.


Greg
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
GregoryWest said:
Hey you are ACE! Worked like a charm. Got the window open, but all the fields (That are coming from the VBA code) are showing up as #Name? Not sure why that is.

Greg
Click to expand...

What do the Control Sources look like?
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
Tied varous things to reference the fields. Here are a few trys:
=[rs_code5]![owneraddr1]
=Mid([rs_datain]![InputText],17,30)
=Mid([RawDataIn]![InputText],107,30)


rs_code5 and rs_datain are the DAO links to the tables, RawDataIn is the actual table (one of them).... All give me #Name? error in the field
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
GregoryWest said:
Tied varous things to reference the fields. Here are a few trys:
=[rs_code5]![owneraddr1]
=Mid([rs_datain]![InputText],17,30)
=Mid([RawDataIn]![InputText],107,30)

rs_code5 and rs_datain are the DAO links to the tables, RawDataIn is the actual table (one of them).... All give me #Name? error in the field
Click to expand...

Forms don’t normally have awareness of recordset objects, so maybe try storing the recordset values into TempVars and then use the TempVars in your form.
 
M

Micron

AWF VIP
Local time
Today, 02:29
Joined
Oct 20, 2018
Messages
3,478
Or just pass the recordset?
(would have to be to a function or sub, I guess). That sub could then open the 2nd form rather than that form being opened as it is now, and receive values from sub. Suggesting that because there are many fields and we don't know how many of them would require TempVars.
Or the 2nd form could have it's record source set to a query. Have to wonder though, what is the connection between the 2 forms and why do so many (?) values need to be passed to it? The 2nd form can't just be an input box I guess (which is modal)?
 
Last edited:
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
Did the TempVar trick, works great. Learn something everyday!!
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
Hey theDBguy, into the testing on everything, and noticed, that yes the tempvar tricked worked fine for displaying the records, when it comes time to edit the records Access wont allow it. Looks like Access thinks the record is locked.


Here is the code to setup and call the form:
working = Mid(working, 1, (Int(Len(working) / 30) * 30)) & Space(90)
TempVars!chaddr1 = Mid(working, 1, 30)
TempVars!chaddr2 = Mid(working, 31, 30)
TempVars!chaddr3 = Mid(working, 61, 30)
TempVars!chaddr4 = Mid(working, 91, 30)
TempVars!inaddr1 = Mid(rs_datain!InputText, 17, 30) '06
TempVars!inaddr2 = Mid(rs_datain!InputText, 47, 30) '07
TempVars!inaddr3 = Mid(rs_datain!InputText, 77, 30) '08
TempVars!inaddr4 = Mid(rs_datain!InputText, 107, 30) '09
TempVars!inaddr5 = Mid(rs_datain!InputText, 137, 30) '10
TempVars!inaddr6 = Mid(rs_datain!InputText, 167, 30) '11
TempVars!inaddr7 = Mid(rs_datain!InputText, 197, 30) '12
DoCmd.OpenForm FormName:="CheckAddr", View:=acNormal, DataMode:=acFormEdit, WindowMode:=acDialog


Here is the code setting up the vars.
Dim chpc As TempVar
Dim chaddr1, chaddr2, chaddr3, chaddr4, chcity, chprov As TempVars
Dim inaddr1, inaddr2, inaddr3, inaddr4, inaddr5, inaddr6, inaddr7 As TempVars
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
GregoryWest said:
Hey theDBguy, into the testing on everything, and noticed, that yes the tempvar tricked worked fine for displaying the records, when it comes time to edit the records Access wont allow it. Looks like Access thinks the record is locked.

...

Here is the code setting up the vars.
Dim chpc As TempVar
Dim chaddr1, chaddr2, chaddr3, chaddr4, chcity, chprov As TempVars
Dim inaddr1, inaddr2, inaddr3, inaddr4, inaddr5, inaddr6, inaddr7 As TempVars
Click to expand...
Hi. I have never tried declaring a variable as type TempVar before. Besides, variable declaration, in VBA, requires a data type for each one. For example:
Code: 
Dim var1 As String, var2 As String, var3 As String
Without the data type specified, the variable becomes Variant. So, when you have something like
Code: 
Dim var1, var2, var3 As String
Only var3 is declared as a String. All the others (var1 and var2) becomes Variants.



You didn't get an error when you did?
Code: 
Dim var1 As TempVar
or
Code: 
Dim var1 As TempVars
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
Nope no errors on the entry:
DIM chcp AS tempvars
The issue I was/am having is when I just DIMed the variables as string they did not show up in the called form. When I declared them as tempvar their assigned values did show up, just not editable it seems.


Not sure if what I am struggling with makes any sense..
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
GregoryWest said:
Nope no errors on the entry:
DIM chcp AS tempvars
The issue I was/am having is when I just DIMed the variables as string they did not show up in the called form. When I declared them as tempvar their assigned values did show up, just not editable it seems.

Not sure if what I am struggling with makes any sense..
Click to expand...
Like I was saying, I wasn't aware TempVar or TempVars is a data type. The name itself implies they are Variant data types. Besides, if you want to use a TempVar, you don't even need to declare them, I don't think. For example:
Code: 
Dim strName As String


strName = "MyNameIs"


TempVars.Add "MyName", strName


MsgBox TempVars!MyName


TempVars!MyName = "NoItIsn't"


MsgBox TempVars!MyName
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
OK, so in the called form, the source for the fields should be TempVar!chaddr1 etc??
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
TempVars.Add "chaddr2", Mid(working, 31, 30)
TempVars.Add "chaddr3", Mid(working, 61, 30)
TempVars.Add "chaddr4", Mid(working, 91, 30)
TempVars.Add "inaddr1", Mid(rs_datain!InputText, 17, 30) '06
TempVars.Add "inaddr2", Mid(rs_datain!InputText, 47, 30) '07
TempVars.Add "inaddr3", Mid(rs_datain!InputText, 77, 30) '08
TempVars.Add "inaddr4", Mid(rs_datain!InputText, 107, 30) '09
TempVars.Add "inaddr5", Mid(rs_datain!InputText, 137, 30) '10
TempVars.Add "inaddr6", Mid(rs_datain!InputText, 167, 30) '11
TempVars.Add "inaddr7", Mid(rs_datain!InputText, 197, 30) '12
Sorry to ask so many dumb questions, this concept is very confusing for me....


I put that in, ran it. Fields are displayed but they are not editable. All the fields/records are DataEntry = Yes, Editable = Yes, Add = No, Delete = No
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
GregoryWest said:
TempVars.Add "chaddr2", Mid(working, 31, 30)
TempVars.Add "chaddr3", Mid(working, 61, 30)
TempVars.Add "chaddr4", Mid(working, 91, 30)
TempVars.Add "inaddr1", Mid(rs_datain!InputText, 17, 30) '06
TempVars.Add "inaddr2", Mid(rs_datain!InputText, 47, 30) '07
TempVars.Add "inaddr3", Mid(rs_datain!InputText, 77, 30) '08
TempVars.Add "inaddr4", Mid(rs_datain!InputText, 107, 30) '09
TempVars.Add "inaddr5", Mid(rs_datain!InputText, 137, 30) '10
TempVars.Add "inaddr6", Mid(rs_datain!InputText, 167, 30) '11
TempVars.Add "inaddr7", Mid(rs_datain!InputText, 197, 30) '12
Sorry to ask so many dumb questions, this concept is very confusing for me....

I put that in, ran it. Fields are displayed but they are not editable. All the fields/records are DataEntry = Yes, Editable = Yes, Add = No, Delete = No
Click to expand...
Hi. The code you posted shows how you assigned values to the TempVars, but it doesn't show how you assigned those values to the form. If the Control Source on your form is bound to a calculated value, like =TempVars!VarName, then it won't be updatable. You'll have to assign the value to the Control. For example,
Code: 
Me.ControlName = TempVars!VarName
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
Good afternoon.



Quick question, is what I am trying to do even possible? I did your suggestion, works sort of, I get the form up, the fields are populated but when I return from the form the TempVars!chaddr1 contains the data from the calling function, not what I typed into the form.

Private Sub Form_Open(Cancel As Integer)
Me.inaddr1 = TempVars!chaddr1
Me.inaddr2 = TempVars!chaddr2
Me.inaddr3 = TempVars!chaddr3
Me.inaddr4 = TempVars!chaddr4
Me.inaddr5 = TempVars!cicity
Me.inaddr6 = TempVars!chprov
Me.inaddr7 = TempVars!chpc
End Sub



Calling program:

DoCmd.OpenForm FormName:="CheckAddr", View:=acNormal, DataMode:=acFormEdit, WindowMode:=acDialog
MsgBox TempVars!chaddr1
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:29
Joined
Oct 29, 2018
Messages
21,467
GregoryWest said:
Good afternoon.

Quick question, is what I am trying to do even possible? I did your suggestion, works sort of, I get the form up, the fields are populated but when I return from the form the TempVars!chaddr1 contains the data from the calling function, not what I typed into the form.

Private Sub Form_Open(Cancel As Integer)
Me.inaddr1 = TempVars!chaddr1
Me.inaddr2 = TempVars!chaddr2
Me.inaddr3 = TempVars!chaddr3
Me.inaddr4 = TempVars!chaddr4
Me.inaddr5 = TempVars!cicity
Me.inaddr6 = TempVars!chprov
Me.inaddr7 = TempVars!chpc
End Sub

Calling program:

DoCmd.OpenForm FormName:="CheckAddr", View:=acNormal, DataMode:=acFormEdit, WindowMode:=acDialog
MsgBox TempVars!chaddr1
Click to expand...
Hi. Remember, a TempVar is a variable for storing data/information. A form is an object for displaying data. If your intent is to type something into a form and then have it stored in a TempVar, then you'll have to assign the entered value into a TempVar. However, to really properly answer your question, could you please describe your ultimate goal? I have been addressing your questions, but maybe we're actually getting away from the ultimate goal and we're actually going in the wrong direction. So, for me to say if something is possible, I'll need to understand what that something is. Thanks.
 
G

GregoryWest

Registered User.
Local time
Today, 01:29
Joined
Apr 13, 2014
Messages
161
No problem, here is what I am dealing with. I have a huge file that comes from the government once a quarter. It contains property onership information. There are 7 records types all stored together is a CSV flat file.


Now on one of the record types is the name an address information about the actual over. Two issues on these records. Even tho each 'owner' (Multiple records for joint ownership) has a unique owner code, the addresses are free form. This means I have to, in program code, make sure all the addresses are correct and there is one per person. Also the government likes to suffix the name field with codes like JT (Joint ownership) 1/4 (one quarter ownership) these all have to be removed.


What this part of the code looks at the owner record. if the record is new (I already have code to tell me if the record is new) then the code break up the owner record and put the information in to the access DB fields. At this point I need the user to be able to fine tune the editing of the new information. Mostly the suffixes of the name, but some times the street address.


I know my first thought was get the base dataset to be accurate. This is a non starter as the government will not/can not change the land titles system.
 
You must log in or register to reply here.

Similar threads

K
Solved Hi I want to VBA Code for Currency to Words in Indian format but One Hundred Thousand instead of Lakh
Replies
1
Views
869
Gasman
Gasman
A
Make a file read only when stored/opened from access database.
Replies
9
Views
1,509
isladogs
isladogs
M
3061 Error, Queries, and VBA
Replies
9
Views
2,345
matt beamish
M
R
Insert Value Not presented in the multi select list box choices access 2007
Replies
1
Views
862
AccessMSSQL
A
K
VBA Module in Excel Spreadsheet pulling info from Access Database
Replies
0
Views
1,429
kesmith
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom