a loop to think about, and I'm doing it wrong (1 Viewer)

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
Hello again people,

I have a bit of a logical problem. the solution seems so incredibly simple, but I don't seem to be able to come up with anything.
there are days:
day1_1, day2_1, day3_1 ...
day1_2, day2_2, day3_2 ...
day1_3, day2_3, day3_3 ...
... ... ...
every day is a textbox with a possible value.
I filtered the textboxes for only those that have a value.
next I want to see which textboxes already had a value to begin with and which were first blank before they gotten a number.
so I check to see if a certain day already had a value before by checking it with a table that keeps track of all textboxes with a value.
Code:
for i = 0 to #days
if new_days = old_days then
'the new day had a value before
else
'the new day didn't had a value before and needs to be saved into the 'database
my very simple problem is the following. lets say that day2_4 had a value before, so this would give
day2_4 = day2_1 (false)
day2_4 = day2_2 (false)
day2_4 = day2_3 (false)
day2_4 = day2_4 (True) ! -> so day2_4 did had a value
day2_4 = day2_5 (false)

Now comes the problem. some days didn't had a value before and just got one entered in them. so I need to upload them. however, even the days that did had a value, like day2_4 return a couple of falses before eventually returning one true. so I am looking for a way that if a day is true, it cannot return a false.

or something like that

my current solution is kinda horrible.
when a day returns True, I save it in a table. then I compare that table with all days that have a value, and those days that aren't in the table are new, but there must be a better way
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
Oh deary me. Finding it difficult to understand what exactly you're talking about. Why would this happen?

day2_4 = day2_1 (false)
day2_4 = day2_2 (false)
day2_4 = day2_3 (false)
day2_4 = day2_4 (True) ! -> so day2_4 did had a value
day2_4 = day2_5 (false)

Wouldn't you just check:
day2_4 = day2_4 (True) !
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
hmm, yes, how to explain?

the name of the day is a code for what that value represents.
Day1_1 means: It's monday and that person is working on assignment 1
Day1_2 means: it's still monday but the person is working on assignment 2
Day2_4 means: it's Tuesday and the person is working on assignment 4

the reason why I check the name of the textboxes is because I need to check for two things
1) does a textbook have a value yes or no
2) is that value New, changed or the same

the first step I do by checking if the name of the textbox corresponds with the name of all the textboxes that were already in use. if True, then it means that (for example) Person X already did work on Monday on assignment 2 but maybe the hours were entered wrong and they have to be corrected.
if false then it means that Person X didn't yet do any work on Monday on assignment 2 and hence there has to be created a new tablerow.

There are other methods to go about this, probably mane that are way more performant then mine, but for some reason my project ended up with this method. in the future I hope to change it (something like
if day2_4.value = day_2_4.value then
'it stays the same
if false then
if isnull(day2_4.value) then
'nothing was entered
else
'the value has been changed

However, as it is, Access is completely flipping. First I couldn't update my table through code, then, I was removing code to see where it went wrong, and as I gradually removed code, it got crashing faster and faster to the point that I cannot open my form anymore.

so first I gotta get my project back working before I can start with this again.

but thanks for the reply
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
However, as it is, Access is completely flipping. First I couldn't update my table through code, then, I was removing code to see where it went wrong, and as I gradually removed code, it got crashing faster and faster to the point that I cannot open my form anymore.

so first I gotta get my project back working before I can start with this again.
:) In most cases it is our mistakes that makes us think the program is giving false results or acting weird.

When you've sorted out your db I think the structure should be like this:
Code:
if len(day2_4.value & "") <> 0 and len(day_2_4.value & "") <> 0 then
    if day2_4.value = day_2_4.value then
        'it stays the same
    else
        'the value has been changed
    end if
else
    'nothing was entered
end if
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
I completely agree, it is usually our fault.

though in thise case, I'm not sure. I completely emptied the VBA screen, the only thing there is "Option Compare Database" and it still crashes, so I must've done something wrong, really deep, somewhere I didn't go yet.

I made a new form and am copying code from my old form to the new one, here and there updating some bad coding and putting things in new functions...
which reminds me, everytime I make a Function or Sub it forces me to catch the result of a function or Dim that doesn't return anything. so I have a variable that does nothing else but catch a return from a function that doesn't have one
check = OpenDatabase().

on the subject of making my project more comprehensible. is it possible to give a second value to a combobox without it appearing?
something like a primary key? I once tried to put the Primary key in the first column and the description in the second, then making the first column invisible. the thing is that when someone chooses a value from the combobox it automatically fills in the PK and not the description.

at any rate, I'm gonna do some copying
thanks once more for all the help, in the newer project I'll probably use your version
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
on the subject of making my project more comprehensible. is it possible to give a second value to a combobox without it appearing?
something like a primary key? I once tried to put the Primary key in the first column and the description in the second, then making the first column invisible. the thing is that when someone chooses a value from the combobox it automatically fills in the PK and not the description.
If you're using the Value of the combo box is the bound field so if you used Me.Combobox.Value then you will get the bound field which is the PK.

So if you want the second column you use Me.Combobox.Column(1). Notice I put 1 for the second column because the columns start from 0.
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
I tried that, but how do you get the value from the second column?
I thought I read somewhere on this forum that it was simply not possible, that you can only read data from the first column and that any other column after the first is to clarify the first
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
sorry mate, you're right.
guess this whole mess makes me braindead :eek:

I tried colum(1) before and I wasn't very succesful in getting the value back then. I must've done something wrong, something small. at that time I didn't really try because I had already an alternative.

as soon as I got my project back running, I'll go and use the multi column because it would make things a lot easier.

thanks once more
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
Goodie!! You're welcome and good luck with the rest of your project :)
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
Hey vbaInet, I've got another question, and slightly off topic.

I think I discovered why my project is going crazy, and it has to do - I believe - with how I code access to my database.

in the top, I define two things. my connection as cnn, and all my recordsets.
Code:
Option Compare Database
 
Dim cnn As ADODB.Connection
Dim recSetCombo As New ADODB.Recordset
Dim recSetAssign As New ADODB.Recordset
Next I let my form load, and when my form load I fill all the recordsets:

Code:
Private Sub Form_Load()
Set cnn = CurrentProject.AccessConnection 
    recSetOpdr.Open _
      "SELECT Emp_Primary_Key, Emp_Name FROM Employees;", _
      CurrentProject.AccessConnection, _
      adOpenStatic, _
      adLockOptimistic
 
   recSetAssign.open _
       "....
        ....

and then there is a third code snippet that I don't really know if I have to do that, but whenever I use any recordset, I start by setting my cnn.

Code:
Private Sub Load_Days_Hours()
Set cnn = CurrentProject.AccessConnection 
 
// use recordset
// use recordset
// use recordset
 
recSetCombo.close
recSetAssign.close

and in the end I close all my connections.
can any of this give Access reason to go insane?
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
That's a topic that's still being researched into but it is believed that it is safer to Close all your connections and also set the object to Nothing. There have been known cases of memory leaks when a connection is left without explicitly closing it but that's debatable. To set the objects to nothing, after the recSetAssign.Close line add:
Code:
set recSetCombo = nothing
set recSetAssign = nothing

Also, when you're declaring your recordset objects you don't need the New keyword if you're going to SET it to a New Adodb.Recordset afterwards. People tend to do it this way.
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
with the huge risc of sound really stupid, what exactly do you mean by that last phrase?

something like this:

Code:
Dim recSetCombo As ADODB.Recordset

and then later whenever I use it, first set it at the start of thes stub/function

Code:
Set recSetCombo = New ADODB.Recordset

and I close (and now I also put them to nothing) after an update, or is it better coding to open and close/nothing at every beginning and ending of a function using that recordset?
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
That's correct.

You close the session (rst.Close) then set that object to Nothing (Set rst = Nothing).
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
hmm, well, apparently it is not the coding of datacode that ruins everything.
is Access 2002 known to be buggy in that respect? something like, if I have 13 loops in each other that the system stops?

at any rate, vbaInet, many thanks for all the help. wasted enough time trying to revive the old database, going to build a new one
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
For the moment I got my form back working. However, now I see the problem with this solution.

Code:
if len(day2_4.value & "") <> 0 and len(day_2_4.value & "") <> 0 then
    if day2_4.value = day_2_4.value then
        'it stays the same
    else
        'the value has been changed
    end if
else
    'nothing was entered
end if


the information in a textbox depends on two things:
1) The Assignment
2) The date
this information was translated into the name of the textbox: Day1_1 and so on.
the old data is collected into an Array, the new data comes directly from the textboxes. what I did was make a new string from the information from the Array and compare that with the string in which information was stored.
Hence this code
Code:
if day2_4.value = day_2_4.value then
wouldn't work, because the information of the original Day2_4 is stored in a table. and I would need to check on two columns (assignment and date column) to see wether the hours compare.
However, with your code I did change it for the better. I don't check on the textbox name anymore, but simply on the value of the table.
I.e.
if date = true AND assignment = true
or somesuch, I don't know yet :D I just got back to where I was on thursday, but I feel its gonna work. access hasn't crashed yet

Cheers all
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
See how that works for you :)

With regards the old data that you're storing in a variable, did you know about a property called OldValue?
 

Riverburn

Registered User.
Local time
Today, 03:53
Joined
Jul 7, 2010
Messages
31
i've seen it, and even tried it (not in this situation though). when I tried, the old value was the same as the new one, so if it were to work in my situation I should know when the old value is updated.
hmmm... maybe I should give it a go, see what the values are with some wellplaced MsgBoxes.

thanks for the hint, i'll try it now
 

vbaInet

AWF VIP
Local time
Today, 11:53
Joined
Jan 22, 2010
Messages
26,374
Have a look in the help files for an explanation before you go writing code. It may not be relevant to your situation. The OldValue of a bound control is the original value of the field before an UPDATE is performed.
 

Users who are viewing this thread

Top Bottom