combo box lookup/autopopulate/subform involved

[MSUKevin]

(v. '97)
I have read previous posts, microsoft support database, help, other examples(northwind, Orders) and I still cannot get this thing to work so I am bring it to you good people for some help, here goes:

I have a form that has 2 catagories of information:
1 - customer info,
2 - transaction details

What I want to have is a combo box that, when an mc# =(unique value), is entered the box either, (a) finds the numbers and autopopulates the other field of customer info, or (b) asks the user if they want to enter a new customer, and allow them to do so on the same form, requery the list, and autopopulate.

I have tried to copy code from help info but I always makes my subform with transactions mess up?

Is it something I'm doing wrong or is it the subform? any one have an idea?

On a totaly different subject:

I am using the DMax func. to trans the transaction totals. The problem is that, if, in order to make it so the txtbox cannot be altered by the user, you have to disable and lock the box, how can you manually enter the first value?

Is there a way, possibly code, to get the first value to be 1, then run the DMax func. after that?

Any help would be appreciated,

cheers

Kevin

 

[MSUKevin]

Pat, first off, thanks for the reply...

Secondly,
I'm not sure what you mean by two field? I thought all I would need is the custmcID field for the combo box because it is a unique value?
I also don't think I am setting up the combo box properly before I even start adapting the code to fit my db. (I am self taught, and have only been taking my first VBA class for 3 weeks!)

Going back to the first set in this process, when you initially set up the combo box do you start with an unbound cmbobox or bound?

If you bind and build the combo do you select:

(a) Access look up the value in table/query
(b) Access will look for a record...

I'm not sure which to select.

Going on further from that, I tried to cut/paste the event procedure from Microsoft suppor article Q161007: Notinlist event procedure, and I am getting a Complie error: variable not defined. I don't understand why this is occuring because I am changing the names of the controls and the private sub name in the event's heading...

Do you know the code, or perhaps where I can get one that will let me:

(a)- open a combo box and select a record, or
(b)- have the notinlist function either allow me to edit the combo box and controls or open another form and enter the data there?

I know this is a lot of questions, (and seems messy), but this is the last thing I have to figure out and its driving me crazy!!!

Thanks in advance for your help,
Kevin

 

[D-Fresh]

My Reply from your previous thread suggested that you run an Insert SQL function in the NotInList event. What didn't work there?

 

[MSUKevin]

D-Fresh,

The SQL statement that you suggested before didn't work. I don't know if it was because the mc# is set to text because it has letters and numbers or not... I would have responded to you previous posts, (which I fully appreciated!) but when I returned from vacation the intern using my office down loaded an MP3 file with bugs, which subsequently fatal errored my machine!! (don't even get me started on that one!)

Due to this, I had to start from an older copy I had backed up, and when I eventually got back to this problem, I had forgotten what had actually gone wrong the first time (please forgive

)

I assume that this task is fairly simple to create and code, but for some reason can't get it to work on my db.... please help!

(BTW, does anyone know the damage estimate of a computer droped out a 7th story window?)

 

[D-Fresh]

Alright, here was my reply and now I see what was going wrong, I think... I assumed MC# was a number not text..

Private Sub Combo32_NotInList(NewData As String, Response As Integer)
Dim MyDB As Database
Dim MySQl As String

Set MyDB = CurrentDb
MySQl = "INSERT INTO [Customers] (CustMC #) VALUES ('" & NewData & "')"
MyDB.Execute (MySQl)
Response = acDataErrAdded

End Sub


I hope this works out better for you...

Doug

 

[MSUKevin]

Doug,

The problem that I see in the future,(going back to the thread on 6/12, is that if I set up the notinlist function this way, how will I get the rest of the controls to autopopulate. In our previous threadd you suggested that I use a subform and set it's property with the after event procedure.

The problem with that is that I already have a subform on my form from which I am using the combo box...

Could I possibly use a pop-up form here? if so, do you know how to set this up?

Again, thanks...

kevin

 

[D-Fresh]

So what I am assuming is that your form does not allow edits? When you have an existing MC# it populates the fields and you want it to do that for a new number too, right? If your form allows edits, when you insert the new MC# in, you can just fill the data right in on the sheet. That will save under that record. The Next time you call that MC#, it will be there. But if you'd like to do it with a pop-up form then... after the execute command open your form. And put this code in before the response=acDataErrAdded line...

while fIsLoaded(PopUpFormName)
doevents
wend


Put this function in a module..

Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function

What this will do, is hold off the original execution of your code until the information is added and the popup box is closed.

Set the Filter of the pop-up form to the MC# you just added and then fill in the data. You should just set your form up to take all the data and have a close button on there.

Now back to your original form, after the Response=acDataErrAdded line, put me.refresh. That should pull up the information you just added. I know this probably seems confusing, but if you take your time with it, it will make sense. I'm sure I didn't explain myself well here, so if you have any other questions, let me know.

Doug

 

[MSUKevin]

Doug,

I think we are getting closer... BUT,
I set my form properties to allow edits? = yes.

I then inserted a combo box to find a record in the table based on the [custmcID] field.

Set Limit to list = yes
Set autoexpand = yes

I then put your code in the onnotinlist event procedure:

Private Sub Combo47_NotInList(NewData As String, Response As Integer)
Dim MyDB As Database
Dim MySQl As String

Set MyDB = CurrentDb
MySQl = "INSERT INTO [Customers] (custmcID) VALUES ('" & NewData & "')"
MyDB.Execute (MySQl)
Response = acDataErrAdded

End Sub

NOW... this is what happened...

first, when I open the combo box and click the [custmcID] that I want to find from the list the appropriate record is displayed. BUT, when I tab to get out of the box and to my next control it shifts my record up one?!?!?!?

By this I mean: John Doe is custID = 2 and custmcID = 44444;
- I select 44444 from the list and John Doe record and info is displayed:
- I tab, once the record is displayed to move out of the cmbobox and the record is shifted to the next customer, or custID = 3, even though it still displays John Does's custmcID of 44444.
(I figure this happens because the other txtboxes are locked into the combo box but How can I bypass then and go directly to the subform if I will need to be able to enter them if a customer has not been entered in the table yet?)

Secondly, When I type in a custmcID that is NOT in the combo box, when I tab I don't get a blank record to input the new information, just the first record in the db...

However, that new number is stored in the combo box the next time I enter it...

Please help, I very confused...

Thanks in advance,
Kevin

 

[D-Fresh]

Alright, first off, you can set the tabstop property to No if you don't want to tab to something. You could also lock all the textboxes you don't want edited... So only keep your subform items unlocked.

Now, how do you have your subform and form linked? Is it by MC#?

 

[MSUKevin]

Doug,
I turned of the tabstop prop. on the combo box, that doesn't keep the user from hitting tab to get out of the text box. I also can't lock the other field's for the customer data because I will need to be able to enter data in thouse when a new customer is added.

My main form and subform are linked through the custID field, NOT the custmc# field. I have the subform prop. data entry field set to yes, so that only records are allowed to be entered, no edits are allowed in the form, only in the table. This is done because the security only allows the supervisor in the tables.

Kevin

[This message has been edited by MSUKevin (edited 06-26-2001).]

 

[MSUKevin]

Please Help!!!

I need a little assistance from you good folks on a notinlist and autopopulate function.

This is what I have so far:

I have an unbound combo box which pulls up an [mcID] numbered record from a customers table. The afterupdate code is:

Sub Combo51_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CustID] = " & Me![Combo51]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Then in the notinlist function I have the following code to open a for for customers named cinfo:
Private Sub Combo51_NotInList(NewData As String, Response As Integer)
Dim MyDB As Database
Dim MySQl As String

Set MyDB = CurrentDb
MySQl = "INSERT INTO [Customers] (custmcID) VALUES ('" & NewData & "')"
While fIsLoaded(cinfo)
DoEvents
Wend
MyDB.Execute (MySQl)
Response = acDataErrAdded
End Sub

When I open the combo box in form view and click a record it switches the form to the appropriate record. But when I try to type in a new number in the combo box I get a variable undefing error message. When I go to debug the line:

Private Sub Combo51_NotInList(NewData As String, Response As Integer

is highlighted and also the customer form [cinfo] in the code is highlighted.

Does anyone know what I'm doing wrong or perhaps how to fix this problem?!?!?
I am not a code writer/programmer and this stuff is really driving me crazy....

Any assistance is greatly appreciated.
Thanks,
Kevin

 

[D-Fresh]

Sorry it took so long to get back to you... What is cinfo? I'm assuming your form? First off, I don't see you opening the form in your code. Also, if cinfo is the name of the form, then you have to put quotes around it, i.e. while fIsLoaded("cinfo"). I would assume that is your problem. Let me know if it's not.

Doug