Please help make my sign in/out work!

[SteveC24]

Hello,

I have a form, which has a tab control on it. On each tab is a different subform for different makes of equipment (only 2 now, and probably only be 4-6 at most). Each of those subforms has in it a text box where you enter the last 3 digits of the asset number. For example, if the asset number is 02123, you only need enter 123 to find it....and I would like to have it do that.

At present, you enter that number, and a few dlookups happily get the users name and various other information. So, it is finding the assets OK, using a little bit of VBA to put the "02" at the beginning of the number.

So anyway, what I then want to do is have the form able to sign this equipment in and out. This is easy enough, but not it would seem doing the asset numbers like I am. I am storing them as text, as they all start with a "0".

This causes havoc with any lookups, and trying to put that number into the ID field of the sub sub form.

Any ideas? Please tell me if I am being too vague!!! I am tired!

 

[bradcccs]

Steve,

You should be able to qualify your CRITERIA portion of you dlookup as text. Sounds like you normally use the ID Number (number), however when you use text, you need to qualify it as such.

Post you Dlookup statement, and I will see if I can assist.

In regards to storing the assett number in the book in/out table, you should store the AssettID (assuming you haven't used the text assett number as primary key)

hth

Brad.

 

[SteveC24]

Hello.

Thanks for the quick reply.


The dlookups are fine and dandy, they work a treat. I will try and explain myself a little better!

I enter the last 3 digits of the asset number into the form, e.g. "123". A little bit of VBA then pops "02" and the "123" together in another text box.

Basically, all I then need it to do is put that number into the AssetNumber field in the sub subform. This would then be saved, and everyone would be happy.

UNfortunately, the assetnumber field in the sub subform is numeric type, whereas the asset number everywhere else is being stored as text, with a primary key as an autonumber.

If I was selecting the asset number here using a dropdown box it would be fine, because then it would properly get the id number etc. In this case it isn't, because we arn't using a combo box.

Any more thoughts?

 

[SteveC24]

Dear lord - never mind everyone!

Sometimes I even scare myself with my stupidity! It has been staring me in the face for HOURS!

I simply used a dlookup to find the AssetID, WHERE the AssetNumber was the same as the one that the form builds with that little bit of VBA.

Jesus, I am thick!


Thanks everyone anyway!!!!