Dlookup issue

[punter]

Hi,

The attached database has a table named Broker_Information_Entry. The second field is named Unique Number. I'm trying to get the Unique Number to populate based on the BOL number entered in the field above. I have tried several different things but can't seem to get the formula to work correctly. It looks like it should be working but it isn't. Any thoughts?

Thank you in advance for you help.

Eddie.

 

[GaryPanic]

afterupdate
fieldname2 = fieldname1
should do it

I could not see an attachment

you might want to lock the field as well otherwise some muppet will come along and edit it

g

 

[punter]

I'm so sorry I didn't attach. Here it is. I'll look into your comments right now.

 

[punter]

I'm sorry but I don't understand what advice you are giving me. I'm sure it works but I'm not sure how it plays into what I'm trying to do. If I put the dlookup formula in the text box shouldn't it pull up as long as I have the formula correct?

Thanks

Eddie.

 

[GaryPanic]

OK - manage to get a quick look at this

you have made a small mistake in your table setup(nothing vital but slightly off the standard dbase names formats)

as a rule have your field names 1 word
no numbers and no spaces so
Unique Number becomes Uniquenumber otherwise when you get to do a bit of coding you have to put [ ] round everything whereas you could just type uniquenumber instead of [unique number]

Access is clever but not that clever it see a space and assumes that they are 2 sepearate things Unique and Number

as we are on it be careful with field names no numbers or spaces as a rule otherwise you need to bracket them


anyway on the after update on your form on the top field you need to insert code
on afterupdate on the rigfht is ... click this and select code copy this in and it will copy BOL_number into Unique number (see how I have had to bracket the field names) you have not done this wrong , but not right either
the Access gurus have hissy fits over this sort of thing , and should you evenr need to upgrade might cause tantrum


Private Sub BOL_Number_AfterUpdate()
[Unique Number] = BOL_Number

End Sub
this does what you have asked for (no idea why you want to do this -)

But this does not give you a unique number which from the look of things is really what you are after

re look at your format

now i understand

what you need is a combo box to do this look at the drop down above BOL select one and it will populate the two field required

seems a bit weird but hey . like the look of the form - but does seem to be a mixture of currencies in there ??



What you need is a combo box check your form out on this zip and see above BOL number you will see a drop down pcik one form the list and it will populate your fields (2 of them)

 

[punter]

Thank you so much for you reply. I will print up your answer and go through it.

Eddie.

 

[GaryPanic]

look at the form - the answer is there - I babble a lot while I am typing - so easier to look at what I have done ..




Field names though you will find a lot of posts on this keep em' together
fieldname not fieldname and don't start with a number

1staddress - will throw awobbly
where as
address1 is ok
I use add1,add2 add3 add4 postcode or zipcode you get the idea

the purest will go even further and the gurus all have their own views

one of the guys can up with standard fieldnames and wrote a piece on it - worth reading -(Must admit i forgot about most of it)

also about how to ask questions the real clever guys want you to be able to show that you have approached the problem - show you logic and then ask

It shows them that you have tried (which you did on this one) you will get better answers by doing this

again i babble

hope this helped anyway
gg

g

 

[punter]

That is further than I was able to get with it. However, I want the unique number to populate automatically. I'm not looking for a drop down list.

Thank you for your help.

Eddie.

 

[GaryPanic]

so what you want is to type in a number 12345 and it get you the unique number Yes ??

without a dropdown

ok so step back and look at it

what happens if you mistype a number ??
what you want is do-able and you can use dlookup - but again what if no number - if you are ok to do this then
its relative easy(ish)
basically you can do this with dlookup - and check samples on this

or you can do a qry
so type in BOL 12345
have a qry run to filter to get UI no
afterupdate run qry then have the answer to your qry populate your required field

???

not the cleanest of ways of doing it -
and dlookup does have its own problems (which is one of the reasons I stay away from it)

g

 

[punter]

Hmmmm. I see your point. I'll have to look into our process.

Thank you for you help.

Eddie.

 

[GaryPanic]

Hey-- just my view, and I am more often wrong than I am right

G

Your approach should work if you got paper to look at and they just type in whats on the paper

the combo does offer a view but aslo they could select the wrong account ??

nothing perfect

 

[punter]

Don't get me wrong. I am most grateful for your help. I didn't do exactly what you did but your help showed me the way I needed to go. I know have it working the way I want it. I would not have gotten there without you doing what you did. That showed me the path I had to walk.

I am most grateful.

Thanks

Eddie

 

[GaryPanic]

Sure -- Didn't take it the wrong way

If it helped - great

Been playing with access for a couple of years and its a great bit of software
,problem is I know it can do stuff , but getting to do it, well thats a different ball game ---

have fun :-}