Populating text boxes on form based on first text box

[justinwright]

I've searched and seen similar, but I can't seem to get any of them to work.

Lets say you have four text boxes on your form
One number data type (in table), we'll call it LookupNumber
Second is a number, ReturnNumber
Third is a date, ReturnDate
Fourth is text, ReturnText

I want to be able to type in a number in LookupNumber and hit a button and it fill in ReturnNumber, ReturnDate, and ReturnText based on what the entry in the table where LookupNumber matches the entry in the table (field name LookupNumber)

I looked at the DLookup, but frankly I had no idea how to structure the different data types (I have a lot, around 24 to be populated, hence the example involving one of each type), plus the fact that I read somewhere that Dlookup might be slow (not sure if this applies with the amount fo boxes I'm populating or not, since a couple of seconds isn't a huge deal).

Any help is much appreciated!

Thanks!
Justin

EDIT:

I also tried something like:

Dim db As Database
Dim rs As Recordset 
 
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM TrackingTable " & "WHERE [LookupNumber] = '" & Me.[txtLookupNumber] & "'")
 
Me.txtReturnDate = rs!ReturnDate
Me.txtReturnNumber = rs!ReturnNumber
Me.txtReturnText = rs!ReturnText

But it errored out on me (something to do with the OpenRecordSet line according to the debugger)

[pbaldy]

Try the following:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM TrackingTable WHERE [LookupNumber] = " & Me.[txtLookupNumber])

[justinwright]

Okay it's bringing it up, but if the value for the field is empty then it will error out

Is there any way to account for it?

I'm using like:

 
If rs!ReturnNumber = Null Then
         If rs!ReturnNumber = 0 Then
                   If rs!ReturnNumber = ""
                             Me.txtReturnNumber = 0
                   End If
                   Me.txtReturnNumber = 0
         End If
         Me.txtReturnNumber = 0
         Else
         Me.txtReturnNumber = rs!ReturnNumber
End if

but for some reason it isn't wanting to do what I need it to . Thanks for the help so far!


EDIT: The reason I wrote the if statement was because if I just put:

Me.txtReturnNumber = rs!ReturnNumber

and the field for that entry was empty, it would error as well.

EDIT2: I read on the help file that it treats a null as a false, so when I put in False instead of Null in the if statement above it says that the following code has an error:

Set rs = db.OpenRecordset("Select * FROM TrackingTable WHERE [LookupNumber] = " & Me.[txtLookupNumber])

I've also tried

 
If IsNull(rs!ReturnNumber) = True Then
Me.txtReturnNumber = 0
Else
Me.txtReturnNumber = rs!ReturnNumber
End if

and

 
If IsNull(rs![ReturnNumber]) = True Then
Me.txtReturnNumber = 0
Else
Me.txtReturnNumber = rs!ReturnNumber
End if

and

If IsNull([ReturnNumber]) = True Then
Me.txtReturnNumber = 0
Else
Me.txtReturnNumber = rs!ReturnNumber
End if

to no avail.

[justinwright]

Erraneous post, thought I had solved it but apparently not :\

Also, would there be a way to look up the attachments? The field is called ATTACHMENT, but I havent the slighyest clue to see how that would work out, and I completely understand if it isn't possible.

Sorry for the constant bugging, things just continually occur to me and I feel the need to add them.

[pbaldy]

If you're saying one of the returned values is Null, I'd handle it with the Nz() function:

Me.Whatever = Nz(rs!Whatever, 0)

If the value the query is using is Null, you don't want to open the recordset at all:

If Len(Me.[txtLookupNumber] & vbNullString) = 0 Then
  Msgbox "Must enter a number"
  Exit Sub
End If

'balance of code

[justinwright]

Well essentially what it's doing is just reading the values based on the lookup number (returning the other fields in that entry in seperate text boxes), and different ones can (and likely will) be null based on each (since its a system where they can go back and add things at a later date, at least hopefully), so how would I deal with displaying that, do you think?

The lookup number will always be entered, but I see what you're saying about not opening the recordset on a Null value, I just honestly don't know how else to approach it.

And I'm sorry I didn't specify, I meant that the values that it returns being null, not the Lookup value (the input by the user will always be entered, it's just a matter of whether the fields that will be returned are null or not and how to deal with them).

I'll try your suggestion and get back to you in a moment though, thanks for helping me so far!

[justinwright]

Wonderful! It worked like a charm! Thank you so much, I feel rather bad that I'm not capable yet of contributing and helping others to give back to the community, but hopefully one day I'll get there.

Also, you help me so much it seems I can't add to your reputation again yet >_<.

EDIT:

I completely forgot to ask, how would you do an attachment lookup? The same way (using rs!ATTACHMENT)? Assuming the attachments are part of the DB itself (attachment field) and not in an external location.

[pbaldy]

Happy to help Justin! As to the attachment, I've never used the actual Attachment data type. I use a text field that holds the path to the attachment. You can try that and see if it works.

[justinwright]

If its a field in the db, how would you reference the path? I just need something comparable that will open the attachment if they click it.

It's in the early development stages at the moment, so I'm open to using the external reference. I can look into it and see what I can figure out, but if it's not possible with referencing an attachment field in the actual DB how would I do the link thing? A lot of the functionality of it depends on being able to look the attachment up (and subsequently click to open).

[pbaldy]

Well, I'm not saying you shouldn't use the Attachment data type, just that I never have. I store a path like:

\\ServerName\FolderName\FileName.xls

And then use one of several methods to open then file, probably most commonly FollowHyperlink. Other options include Shell and:

http://www.mvps.org/access/api/api0018.htm

[justinwright]

I'll probably be using FollowHyperlink then, since the link you just posted completely and totally baffled me; I believe at the moment that's a good ways over my head.

How would you list the location of the file in the text box then? That way I can list it and use the FollowHyperlink thing (probably linking that part to a button next to it for "Open Attachment" I guess, I haven't quite figured that out yet).

[pbaldy]

If you didn't like the last link, you're going to hate this one.

I use this to let users point to the file:

http://www.mvps.org/access/api/api0001.htm

and then just save the resulting string in the field. Both links I've given are basically cut/paste into a standard module, and then call as described. While it's good to understand the code, it's not a requirement to use it.

[RuralGuy]

You'll have to admit Justin that that Paul is quite the kidder isn't he?

[justinwright]

Enough to make my brain explode

I'll go ahead and mark this thread solved though, since I created a new one for the attachment problem. Thanks for the help!

[justinwright]

Sorry for the double post, but I can't find the thread I just made o_O