HackinCowboy

[jalbes]

Wow, I couldn't thank you enough. I opened up my e-mail and I nearly @#$& my pants. I utilized your code but now I face a new hurdel that I'm sure that you can help me leap over.

Heres my dilema:
when I select an address or unit number and press find, I want all the information to show up in a new form. So I know that I'll need a module. In the module I inserted:
Option Explicit
Dim dbTenant As Database
Dim rsTenant As Recordset
Dim rsTemp As Recordset
But is there a specific command that I use to access the module, or does it already assume that all the forms are linked?

Like with this line: txtUnit.Text = rsTemp("Unit Number") in your program it pastes the data from the temp recordset to the "Unit" text box.
But assume that the "Unit" text box is on another form, what line do I use to paste to that new form?

If you don't understand, I don't blame you, So I put up what I have so far so you can see what I'm talking about.

Thanx,
Shaun

[HackinCowboy]

I checked over your code so far. First, when using a module, you declare variables as Public instead of Dim. Also in your form, you do not need to re-declare the variables at the top. I would also declare Address and Unit as globals. This way when you assign them values from one form, those values will still exist in another. I've made some changes in your code to show you what I mean.
Good Luck,
~HC

[jalbes]

where you stated "you declare variables as Public instead of Dim." I totally forgot all about that. Someone else on another forum told me that a while back. Ooops! LOL

And I knew that I didn't have to re-declare the variables at the top. I had just left them there from when I used your code and was trying to figure out the module.

But when you said "I would also declare Address and Unit as globals. This way when you assign them values from one form, those values will still exist in another." I'm a little unsure what you meant. So whats the difference between Public and Global?

Thanx again.

[HackinCowboy]

Sorry that was kind of misleading. Global just deals with the level of availibility a variable has. Global is the highest. Public means that it can be accessed by any other part of your program. So to keep it simple, Public and Global are the same. When you declare variables you just say "Public variable name As type"

[jalbes]

Ok, so here I am, inserting code:
Set rsTemp = dbTenant.OpenRecordset("SELECT * From Tenant WHERE (Address ='" & sAddress & "')")
txtAddress = rsTemp("Address")
txtUnit = rsTemp("Unit")
txtCity = rsTemp("City")
txt... you get the idea...
And I decide to try the program, I select an address from the combobox, press find and then I suddenly get an error stating:
Runtime error '94':
Invalid use of Null
So I press debug to explore further and the line:
txtCompany = rsTemp("Company")
is highlighted. So I double check for spelling, correctness. Then I decide to open the table in access to see if the problem lays within there. The only difference that I found that could be associated is that the "Company" column is empty or "Null".

Therefore I believe I need to make it so that certain text boxes aren't required and can show up empty if my batabase feild is also empty. Any suggestions?

[HackinCowboy]

Try going into your database and set the Company Field property Allow Zero Length String to YES. This will allow you to add a record without having to enter in a value for this field. Anytime you have a text field that may not always have values entered in them, set them to Allow Zero Length String
Hope this fixes it. If not I can look more into it.
~HC

[jalbes]

really weird.

I tried what you sayed, and it was already set that way from when I created the DB file. I even converted the file from access '97 to 2000 and still no go.
I tried making a default formatting thing in access so when you see the "Date" it would show up as "dd/mm/yyyy;;*". Still wouldn't work.

Argh...!

Any ideas?

[jalbes]

Well I finally figured something out for my self, LOL.
I was getting really disgruntled so I strolled over to Chapters and read a few VB books. 5 hours later I decided to leave (without purchasing a book!) and felt even less intelligent from when I first went in. So I went over to VB-World and did a search. I looked through a few threads and I finally found one that caught my attention. Someone had the same problem and the fix was as simple as this:

What I had before:
txtPostalCode = rsTemp ( "PostalCode" )

And here was the fix:
txtPostalCode = rsTemp ( "PostalCode" ) & ""

Thanx for your help anyway.
Shaun