combo box value import

[cstanley]

Hello all,

I have a form with a command button that will open up another form, which will allow a user to enter data. Both forms have a common field "System Number". I would like the forms to be synchronized, such that the value from the system number field of the first form is set as the default value for the second form when it opens. Is there a small bit of code that will do this?

Thanks,

Chris

 

[Dave Eyley]

Do you mean something like -

On the button to open the 2nd form -

DoCmd.OpenForm "Form2", acNormal,, "[System Number]=_" & Me![System Number]

Provided that Form2 has an underlying table or query with the right data, then it will open Form2 and display the relevant record.

The example above assumes that the data type is a numerical one and not a string.

If the data is a string variable then the syntax would be -

DoCmd. OpenForm "Form2", acNormal,, "[System Number]=_" & Chr$(34) & Me![System Number] & Chr$(34)

This adds another set of quotes around the value for correct syntax.

By the way, it is better not to have spaces in formnames, querienames or fieldnames. It may look more userfriendly but it can create havoc especially when you forget to put the [] bracketss around the name.

In the lines of code above I have used the _ symbol to signify a SPACE.

Hope this is of help...

Dave E

 

[Stephanie T.]

Hope you two don't mind me butting in on this thread.

Dave - I used your code and it worked great thanks! I have one other question.

Most of the time the data entry person will open form 1 (frmDiscountsTaken), and put in information - specifically the ID number (MCB). Then they click the command button that opens form 2 (frmMCBs). I'd like the ID number (MCB) to automatically fill the ID number (MCB field) on form 2. Your code worked great if there was already information with that ID number in form 2.

So, do you know what code I need to add so that if there isn't anything in form 2 with the current ID number is will auto fill, but if there is already information with that ID number is will not be overridden?

Any help is appreciated.

Thank you,
Stephanie

 

[Dave Eyley]

I have created a small db (see attached) which illustrates the process of -

1. Opening a 2nd form using criteria from the 1st form.

2. Demonstrates how to get the link field autofilled, even if there is no record in the 2nd table.

I am, of course, assuming that the link field is the one that needs autofilling when the 2nd form opens. If not, I'm going up the wrong path.

It does involve the second form having a subform as I've not explored other options that may work as well. I bet there are though.

I hope this is what you are looking for.

Dave Eyley

 

[Stephanie T.]

Dave,

That is exactly what I want to do. The second form does have a subform, however the field I need auto filled is part of the main form. My problem is that I cannot open your attachment. I am using Access 97, if that could make a difference.

Is there another way of providing that info or would it be easier to email the db to me at stephanie@pamelasproducts.com?

Thanks,
Stephanie

 

[Dave Eyley]

Stephanie,

I used Access 97 at home and at work. I used WinZip to zip the database and sent it to myself at home where it opened fine.

I will post it again, but this time I will not zip it. When you download it, it will be called DB.zip, change the .zip back to .mdb and try opening it again.

Dave Eyley

 

[cstanley]

I've tried to use this code, but get a syntax error on [cbo123]... it's the correct name of the combo box - what am I doing wrong?

Thanks,

Chris


DoCmd.OpenForm "frmMain", acNormal, , "[cbo123]=_" & Forms!frmkeywordsearch![lstSystem]

 

[Dave Eyley]

I'm a little lost here....

DoCmd.OpenForm "frmMain", acNormal, , "[cbo123] = " & Forms!frmkeywordsearch![lstSystem]

looks OK...

The underscore you've used after the = sign is just to let me know there's a space there, right?

There must be something wrong, but I can't see it.

Could you post me a copy of the database so I could have a play?

Dave Eyley

 

[cstanley]

No - it works when I remove the _ and put in a space like you said - duh-uh....

But when I run this, I just go back to the form and the value doesn't appear - maybe it's because the original form doesn't get closed? This is related to the problem I'm encountering in thread

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=35390

The dbase is 8MB - too huge for import, unfortunately... any help would be appreciated!!!

Thanks,

Chris

 

[Dave Eyley]

Right...

Let's check we're on the same wavelength here...

You've got Form1 populated with an underlying table/query.

One of these fields is a combo that when selected is used to open another Form (Form2) with data that is related to the combo field on Form1.

When you select an option fron the combo Form2 opens but there's no details?

Or Form2 doesn't open?

Dave Eyley

 

[cstanley]

Here's the deal...

Form 1 opens Form 2. There's a text box on Form 1 which is used as a search criteria to display the records on Form 2. This part works wonderfully.

The user selects the record on Form 2. I would like the value from that record to go back to Form 1 into a combo box, which is used to filter the records onto Form 1.

When I use the code that we are talking about, Form 1 pops up, but with no value in the box where I want to put it, and then it shows "Record 1 of 1 (Filtered)"

Thanks!

Chris

 

[cstanley]

Oh, and the form is blank

 

[Dave Eyley]

OK, (I think)

It's a bit complex....

Can you explain the use of the combo, since if you take one value back to Form 1 and put it in a combo, what's the combo used for if it's only got one value in it?

I'm English, remember, you've got to lead me along gently...

Dave Eyley

 

[cstanley]

The combo is used to filter the form to display the desired record. The code behind it is :

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SystemNumber] = '" & Me![cbo123] & "'"
Me.Bookmark = rs.Bookmark

Chris

 

[Dave Eyley]

So the combo gets a value in it and then runs the code which does the record selection?

1. Why a combo? Why not just a textfield?

2. What runs the code? AFTERUPDATE? ONGOTFOCUS?

3. Are the records on Form 1 in a subform? or is the combo in the header and the fields in the details section?

Perhaps, if you could explain what the process is supposed to do, I might understand where we're going with this...

Dave E

 

[cstanley]

Dave,

I've tried to send it to a text box which can do the same thing, with GotFocus triggering the code, but the same thing happens. The records on Form 1 are part of the form, not a subform.

I'm just trying to paste the value from Form 2's list box back into Form 1 and have it search based on the pasted value.

Thanks,

Chris

 

[cstanley]

Please help if you can!!!

Chris

 

[Dave Eyley]

A field on form2 should be able to send its value to form1 without any problem, it should be like -

Forms!Form1!Field1=Forms!Form2!FieldX

It is important, however, how the code is executed.
If it is in an AFTERUPDATE property on the FieldX form, and FieldX gets filled in by a query, then the AFTERUPDATE property may not work. I think I've had that problem before.

It may be worth putting the code in the ONCLOSE property of Form2, so that it returns the value to Form1 when it closes.

Once the field in form1 has got the value, you say that the ONGOTFOCUS will trigger the search. Are you sure it will?

To check it, try putting a Command Button beside the Field on Form1, temporarily, to test the search routine. Put the code that you have in the ONGOTFOCUS in the button's command.

If it works with the button and not the ONGOTFOCUS, then you've got to find a way to trigger the code somewhere else.
You can't use the button as a permanent measure unless it's made invisible until you fill the field from Form2, which is a possibility but not a good solution.

HTH - and I'm still around.

Dave Eyley

 

[cstanley]

You were right on all counts - this line of code does export the value, and it doesn't run the search.... any quick & dirty ideas to trigger the code?

 

[cstanley]

I've tried this, on the OnClose property of Form 2:

Forms!frmMain!cbo123.SetFocus

And it gives me an error message that it can't set the focus, and when I stop the debugger, it goes ahead and sets the focus anyway and runs the search. Why do you think that is?

Thanks!

Chris