Filling a Form when data does not already exist

[lubbs65]

So, i have a form that inputs data into a default table. One of the fields is "names". They select names via combo box. Let's say the user wants to enter the name "John Smith" which is not in the Combo Box (it doesn't already exist but needs to). Upon hitting Enter I want the message "The text you entered isn't an item in the list. Do you want to edit the item in the list?" to open.

I know the default tasks database allows this ability so I'm sure I can do it in my custom database. Any ideas?

 

[lubbs65]

Okay, so I have almost cracked the entire problem. Here is what I've done and will be referencing for comaprison.

If you open up the default "Tasks" database that access provide and open up "Task Details" you get a combo box. You can obviously select contacts that exist in the table. If I wanted "John Smith" and typed in "Smith" which DNE, It takes me to the next form because:

In the property sheet for "Assigned to" under the Data Tab, it goes to "Contact Details" thereby bringing you to the Contact Details form to now insert the contact.

Upon entering in the new contact into Contact Details the "Task Detail" form is still open and has the new name already in the "Assigned To" Combo box. Incase you haven't been following along, I am trying to something nearly identical to the default databases idea except i have to just expand upon it by a lot.

Here is my problem:
by emulating this all the way (or so i feel like i have but clearly something is off), I still have one issue. For my version of combo box of "supplies",
if i type in a new supply, I go to an edited form from my list of supplies rather than default opening to a new page. How can I fix that? If I can fix that, I am done.

Previously, I was using event-> On not in list->
The problem I was having with that was that after typing the information into the second form, i was not able to figure out how to properly refresh the first form. Therefore, the supply entered into the supply form still did not exist in the base form.

This problem does not exist when using "not in list". However, by using "not in list" i was able to open the "supply form" to a new page. Both methods pretty much have opposite problems.

 

[Norie]

You refresh a form with this.

Me.Refresh

Where Me refers to the form and will only work if the code is in the form's module.

What form are you actually opening?

Are you actually trying to add a new supplier?

Why not close the first form when you open the 2nd one?

Then when you close the 2nd form you can open the 1st form to the record you were just working on.

 

[lubbs65]

but which form would me.refresh refresh and is there a refresh all macro to just make things easier?

I have a base form that has multiple combo boxes in it.
Supply form would be one of them. Its supposed to be set up so that if the user is trying to enter a supply that doesn't exist, the supply form would enter so he could add that supply to the supply table.

so when would i want to execute the Me.refresh macro? After entering data into the supply form, upon entering the data into the base form but before opening the supply form?

i am trying to add a supply

its just supposed to be set up so you open base form then supply form if the supply DNE, then add and close supply form then finish entering into base form. then close base form.

 

[Norie]

The Me.Refresh would refresh the form that Me refers to which is the form the code is in.

eg If Me.Refresh is in the Form1 module Form1 will be refreshed, if it's in the Form2 module Form2 will be refreshed...

You wouldn't need to use Me.Refresh if you opened and closed the forms.

If you were to use it would probably be after you've added the new record.

How exactly are you opening the form where you want to input the details for the new supplier?

 

[lubbs65]

i have the form open now ftrom on not in list->macro->action->open form

I'm still having trouble understanding when to open the refresh macro though.

i wrote this:

Function Refresh()
Me.Refresh
End Function

The debugger is telling me this is wrong though so if you can explain whats wrong with the code that would be great.
when should it get run though?

right now i have it set to run through a Macro on event->on got focus
then it calls the function refresh.


the idea being when referring to the base form it will always refresh regardless of whether or not it has to call supply form. which shouldn't be a problem right?

 

[Norie]

What 'refresh' macro?

I'm sure I mentioned where the refresh should go - the form's module.

By the way are you using code or macros?

Macros and code are different, and it's become unclear, for me anyway, which you are using.

It's quite hard to help without the right information.

 

[lubbs65]

i've been using macro's. i set a macro to run the code. without that one function, my program now has no lines of code.

where can i go to see the forms module?

 

[Norie]

Open the form in Design Mode, select the combobox and then View>Properties.

Then goto the Event(s) tab on the property sheet, find Not On List and click the ellipsis(...).

Now you should get a dialog where you should choose Code Builder.

That should open the VBA Editor to the form's module and you should also see something like this.

Private Sub Combo1_NotInList(NewData As String, Response As Integer)
 
End Sub

This is where you should put the code.

 

[lubbs65]

I already have it set so in that not on list runs the macro to open the supply form as a new page (rather than editting). my previous vba attempts at opening up supply form were successfull however they did not open to a new page. it would always go to edit one that already exists. so if i were to stick with my current macro approach should i just run a macro runcode (then call the function)? that is what i have already done and it didn't work. so how can i have both that function run to run the update, and open up the second form?

 

[Norie]

Sorry I don't know much about macros, apart from one thing I suppose - macros and VBA don't mix that well.

Use one or the other not both.

If you have macros you might be able to convert them to code and if you have code why not just add to the code to do the rest? eg open the form

Opening a form in Access VBA is very straightforward, and opening a form to a particular records isn't much harder.

One thing I think you might not quite be getting is that the code for the not in list is creating a new supplier record, albeit with only the name field having data.