=If(I knew it exactly"I wouldn't ask", so here is it)

[AidanEnos]

ok...

I am trying something new for me here...

I have a drop down list box that is populated by a column in a table - customeridentifier. (I am using customer identifier so we can make names that include abbreviated addresses "ABC_NYC")

So here is my literal if statement - some cleaning up so it works would be great

=ifnull(Open.form "customeridentifierinputform", if a customer IS selected from the list take the corrosponding record data from the CustomerIdentiferTable and input those values to display on this form in the fields (CustomerName=Customer, Address=Address, City=City, etc.)

I think that makes sense... if the user picks nothing then it opens the new customer input form, if they pick a name then it fills in all the relevant information.I know there is a way - and it's much better than that!!!

 

[GingerR]

Ok, how about this instead:

A user hits a button.
If the value of the combo box is null, a form opens ready to accept new data (enter a new record).
But if there is something chosen in the combo box, the same form is opened but with the data from the table for the item chosen in the combo box.

Ok?

So you have a combo box called cboList.
You have a form called frmMain.
you can a button. in the button's OnClick event, you put:

if isnull(me.cboList) then
   docmd.openform "frmMain",,,,acFormAdd
else
   Docmd.OpenForm "frmMain",,,"CustomerIdentifier = " & Me.cboList,acFormEdit
end if

This is assuming your CustomerIdentifier has a number for an ID. If not, the second part is

Docmd.OpenForm "frmMain",,,"CustomerIdentifier = '" & Me.cboList & "'",acFormEdit


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AidanEnos]

So...

It's not working, I have a combo box balled cbolist

I have created an Entry in the list called "none" (the list comes from a query on "CustomersTable" and uses the field "CustomerIentifier"... but I digress.

I have made a new form, it's record source is the same "CustomersTable" and has fields such as CustomerName, City, Etc..

I have created a button called "RefreshCustomer", on click it has Event Procedure...

Private Sub Refresh_Customer_Click()
On Error GoTo Err_Refresh_Customer_Click

If IsNull(Me.cbolist) Then
DoCmd.OpenForm "CustomerIdentifierInputForm", , , , acFormAdd
Else
DoCmd.OpenForm "CustomerIdentifierInputForm", , , "CustomerIdentifier = " & Me.cbolist, acFormEdit
End If

Exit_Refresh_Customer_Click:
Exit Sub

All of this so far has given me zero functionality. I select a name from the list and it doesn't update the other fields - whether I click the button or not, if I leave it blank and click the button it does nothing at all. When I move to a new record or back a record the combo box - being unbound keeps the last choice I made in it - regardless of anything, short of me making a new selection - which has no impact on the other fields on the form. I'm loving the new look and the ideas are great - just need to get 'em workign!!!

 

[GingerR]

Hi--a variety of things going on here:

You say you have a button called RefreshCustomer however your code is for "Refresh_Customer" please ensure the button really has the code in it's CLICK event.

I'm not sure why you have this combo box on a bound form (a form that has a recordsource)?

Also now you have a choice of "NONE" in the combo box, so your statement shouldn't be

If IsNull(Me.cbolist) Then

it should be

if me.cbolist = "NONE"

However I want to know what the RowSource for your combo box is; it should be a UNION query with your table and with ID = 0 and Customer = "NONE".

Can you open the form directly by just double-clicking on it?

If CustomerIdentifier is TEXT you have to change the string to have single quotes:

"CustomerIdentifier = '" & Me.cbolist & "'"

Further Testing: If you remove everything but

Docmd.OpenForm "CustomerIdentifierInputForm"

does it work?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AidanEnos]

YAY - progress!!!!

Private Sub RefreshCustomer_Click()

If Me.cbolist = "NONE" Then
DoCmd.OpenForm "CustomerIdentifierInputForm", , , , acFormAdd
Else
DoCmd.OpenForm "CustomerIdentifierInputForm", , , "CustomerIdentifier = " & Me.cbolist, acFormEdit
End If

End Sub

Ok, this works for customer none... it opens the CustomerIdentifierInputForm and I can input a new customer just fine.

The new customer doesn't seem to update in the list in the combo box and if I select a name from the combo box it works like a Query Input and window pops up that says "Enter Parameter Value _____" (whatever name I selected from the list.

I tried changing the code to what you mention above...
"DoCmd.OpenForm "CustomerIdentifierInputForm", , , "CustomerIdentifier = '", acFormEdit"
...it forces to me use the debugger

To answer the other questions...
"I'm not sure why you have this combo box on a bound form (a form that has a recordsource)?"
The form is to Input customer orders, so the data has to be kept somewhere... hence a recorsource.

"However I want to know what the RowSource for your combo box is; it should be a UNION query with your table and with ID = 0 and Customer = "NONE""

The Row Source is Table/Query, it's text so I can make names like AT&T_Iowa. I prefer the CustomerIdentifiers to be at least partially readable for ease of use.

So... yes - if I use none it works... but that's where the successes stop so far

You are awesome... I know you can guide me to victory on this!!!

 

[GingerR]

So if you are on a form where the recordsource is the table you are entering data for, then why do you want to open ANOTHER form that has the same data in it? Seems to me you'd want to do this:

Make a button that says ADD NEW which goes to a new record on the same form

Or

Pick a customer from a combo box and have THE FORM YOU ARE ON move to that customer's record.

Isn't that all you are doing?


If so, for the combo box, do this: put a new combo box on the form from the toolbox. When the Wizard comes up, choose the third option (Move to the selected record or whatever it says) and go thru the wizard. It will take care of the 2nd thing above.

Also then put a button on the form and follow the wizard to ADD NEW RECORD.

As for your comments above,

DoCmd.OpenForm "CustomerIdentifierInputForm", , , "CustomerIdentifier = '", acFormEdit"


is not right.

It should be

DoCmd.OpenForm "CustomerIdentifierInputForm", , , "CustomerIdentifier = '" & Me.cbolist & "'", acFormEdit

However, again, seems like you are wanting to do something so straightforward, but are going about it in a twisted way )

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AidanEnos]

Everything I do starts as straight forward and ends up in a twisted way....

Based on your advice I changed what I was designing entirely... I thouhgt I had made that clear....

My new form now looks like...

top half of page is Customer info in a seperate table called CustomersTable. This table has all the basic stuff with a "smart name" I tagged CustomerIdentifier. When the user wants to input a new order they open frmMain and choose the smart name, that should automagically fill the rest of the relevant info when they click the "RefreshCustomer" command button. If they can't find the smart name they want in the list then they select "none" which opens the form "CustomerIdentifierInputForm" (this is the part that works).

There are 4 tab controls on the bottom half of the page containing all of the detail I previously had on 3 seperate forms (per your advice). They are all bound to "HS_OrderScopeTable". This table currently has all of the original customer basic stuff still in it (including a customeridentifier field).

Now, here is the scenario - user opens the frmMain, it opens as DataEntry to a new record. They click the combo box and select "none" a new form pops up so they can add the customer info - that information gets added to the CustomersTable, THEN the newly generated "CustomerIdentifier" should be in the combo box list (it doesn't seem to add the new name to the list until I close and re-open frmMain - that being said I'm sure on my "Save Record and Close" button I can add the doCmd.Close "frmMain" doCmd.Openfrm "frmMain" and it will be transparant to the user opening and closing but the list will be updated)

Ok, so the user selects the "Customer Identifier" name and it fills in the address and such, the user moves down to the tabs and starts inputting Order data. When this order data is saved - I'd like all of the data on the form to be saved to the "HS_OrderScopeTable", but only the "CustomerIdentifier" needs to be saved in that table as it's already in the "CustomersTable" and can be pulled out for a Report based on the saved selection in the order record.

I hope that clears up all that I'm trying to accomplish here - and you are right... I make things difficult, mostly cause I'm trying to figure it out, and am remembering bits and pieces from when I've done this before - but a decade is a mighty long time, longer than I thought when I took on the task

 

[GingerR]

As for your combo box "refreshing" - in the OnEnter property, put an Event Procedure and put

Me.cboBoxNameHere.Requery

This would help us:
What is your table structure for your two tables?
What is the recordsource for each of your forms (and any subforms)?

Better might be having a "Switchboard" form, and unbound (no recordsource) form where the user picks a customer from a combo box and your frmMain opens from there. If the customer they want isn't in the db, it opens a form to enter the Customer-specific data, then a button on there can open frmMain with the order info.

So if the recordsource of your form is not the main table, in the OnChange event of the combo box you can put:

Me.txtCustomerName = dlookup("CustomerName","CustomerTable","CustomerIdentifier = '" & Me.cbolist & "'")

etc.
This will go get the data from table "CustomerTable" from the field "CustomerName" for the Identifier = whatever you picked in the cbo box, and put it into the text box txtCustomerName on your form.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[AidanEnos]

What is your table structure for your two tables?

Maybe I'm thinking you want too much into this question - but I don't understand what you are lookign for with it. My table structure is very basic, I have a single primary key and most fields are either Number or Text. A fairly simple design.


What is the recordsource for each of your forms (and any subforms)?

I have no subforms.
frmMain = HS_OrderScopeTable
CustomerIdentifierInputForm = CustomerTable


I do have a switchboard Type form I have named LaunchPad, it's what will be used with some simple command buttons on it - one button will be (actually is) open frmMain. Another will deal with a seperate form that is specific user information, a few others will run some reports that are needed. Nothing too complicated.

 

[GingerR]

structure" i mean:

TableName: X
FieldName: ID (Autonumber)
FieldName: CustomerName
FieldName: CustomerAddress

along with some sample data (of items only pertinent to your problem.

I'm just trying to help. In the signature of my post are helpful links to get the most out of this forum. Since I do not have your database sitting here on my computer, I can only go by what you tell me. Obvious to me is that if the second part of the code above doesn't work, something isn't named right or called correctly or something, and without details of what you are doing I can't figure that out for you.

Have you tried the things above, like requerying the combo box? Did it work?

What is the code behind the button that is calling the second form? Are you sure the field names are spelled correctly (I cannot see it, but if you tell me the field names in the table I can double-check, sometimes another set of eyes helps). Did you try the code to "fill" your form's fields with data from the customer table? Did it work?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html