relationshipping again...

[Tech]

sorry about this,

I have created a subform in a form.

this subform holds the employee number.

how can I make a relationship where it can copy some values (like a customer number in the customer subform included in the main form) into the employee's table? (Like I have done previously, with the help of u ppl)

 

[The_Doc_Man]

Either you were inconsistent in the field names you used to ask your question or you are trying to violate normalization rules big-time. I can't tell which. Rephrase your question.

 

[Tech]

it's probably best if u looked at it:

ftp://temp:temp@techftpserver.co.uk/mdffc.zip

I want it where the employee table has the order number, customer number, perhaps the details of the order in the employee table. that's all. similar to the order form table (using relationships)

 

[Tech]

firstly, the problem is that, in the main order form, if we select an employee number, it does select it but doesnt store it there, it just doesn't store it's value in the database at all, why?

I want it to do this. because i want it so that some employee "manages" a particular set of companies/customer number.

any ideas?

 

[WayneRyan]

Tech,

Your Orders Table does not have a place for storing the
employee who presides over it.

Your Orders Form does not have a place for storing the
employee who presides over it.

Your employee subform is bound to the employee table
but it has no relationship to the main form. Its combo box
is just a list of employees.

You need to move the employee ID to its proper place in your
forms and tables.

hth,
Wayne

 

[Tech]

tyvm

so, basically create an employee name and number in the order form table and maybe do the combo thing (like the customer details type combo) and then.....?

or is there a way to do it in a subform way too? which way do u think is best and how can it be achieved?

 

[Tech]

can't do it I tried!

i put in employee number in the order form table.

made another table in design view containing employee number and employee name.

the in the main order form, i put in the employee name and number in the SQL Statement of the main order form

I think dragged the name/employee number from the field list into the main form.

I then changed the employee number into a combo box and did row source and made it unbound in the control source.

and then when I ran it, well, all the data in the form was gone, so if I deleted what I just done, all of it came back!

what the hell is going on? all I want is the employee name and number in the order form so at the end of the day I can store this in the table and maybe make a query to search the employee number, order number and customer number!

 

[Rich]

You need to set up your tables and relationships correctly first, like I keep telling you, look at the samples and stick to one thread so others know what's going on

 

[Tech]

yes but HOW to set them up correctly? this is where I need help.

 

[Tech]

right so, I made a new table called empdet. contains employee name and number.

then went into the main order form. added a combo box to look up values in that table (employee number)

and then added a text box to do:

=[combo246].column(1)

to show the employee name that has been selected as a employee number (data comes automatically)

ran it great.

one obvious problem:

doesn't keep it in the database, all values are the same even if u change the employee number.

how can I solve this?

 

[Tech]

just now, made a relationship like this:

added the employee number in the customer details table (as I want the employee to manage a particular customer number(s))

then add the empdet table in the relationship and linked the employee number from the empdet to the employee number to the customer details.

great but of course, doesn't save the data as appropriate.

ftp://temp:temp@techftpserver.co.uk/mdffc.zip

y?

 

[WayneRyan]

tech,

It doesn't have to be this difficult ...

If you want an employee to preside over an order you
must have a setup like this:

tblOrders:
OrderID
OrderDate
OrderDescription
OrderStatus
EmployeeID


tblOrderDetails:
OrderID
ItemNumber
ProductID
Quantity
UnitPrice

tblEmployee:
EmployeeID
LastName
FirstName

tblProducts:
ProductID
Description
Manufacturor

In the first table, your form can have a combo box
for the employee, two-column (EmployeeID, LastName)
and it will store it in your table.

In the second table, your sub-form will display
only the detail items for the parent OrderID.
Each line item will have a product (ProductID) that
is defined in your table tblProducts.

In the third table, there is no form (sort of) but
it contains the info about the employee presiding
over the Order.

The fourth table, like the third, just defines the
info about each product.

If you set them up similar to this, life will be
a lot easier in the long run for you.

I replied to your other post (in another thread)
and your employee info was just not included in
your main form. Just having it on an unrelated
subform does not relate it to the order.

hth,
Wayne

 

[Tech]

thanks for that, i am trying it out now

I don't follow this part:

tblOrders: 
OrderID 
OrderDate 
OrderDescription 
OrderStatus 
EmployeeID 


tblOrderDetails: 
OrderID 
ItemNumber 
ProductID 
Quantity 
UnitPrice

the first table, do I have to create it? or is this like a "cut down" version u typed just to save time?

It's the customer number I want to associate with the employee ID....I will try to implement it using what u have said.....thanks alot

 

[Tech]

getting a bit confused here:

created the employeetable and made a form using the form wizard.

added EmployeeID in the customerdetails table.

then, I went in design view of the order form. Now, should I ADD the employee subform? or should I create a combo box manually? or should I add the entry in the customer number combo box to add the employee Number/ID and the employee lastname/firstname?

stuck

update: if I add the employee tbl in the customer order combo box, (in the row source of the combo box), which is in the order form, it doesn't like it....ok, so that suggestion out.

 

[Tech]

ok well I added the combo and text box manually in the order form and looked up values in the tblemployees.

great

but again, it doesn't store the values in the database, any value u select overwrites the previous record values of the employee ID and it don't store it in the database!

 

[Tech]

nearly got it!

I dragged the employeeID from the order form field list to the form detail, changed into a combo and selected the row source from the employeetable........

But it doesn't like showing the Lastname! only the first name

still working on it...

 

[Hayley Baxter]

Hi Tech

I haven't seen your db because the pc I am working on just now doesn't have winzip. If you need to look up the surname as well as the first name just add the id, first name, last name to your combo and set the column count to 3 and you will probably want to hide the column containing your primary key so just set this to 0.

HTH
Hay

 

[Tech]

ty!

 

[Tech]

the person said that, which ever employee places an order is responsible for that customer.

so, should I put in the employeeId in the customerdetails table?

IF so, then how do I do the same thing like i have done, just now, with the order form table (taking the employeeID field and making it a combo box and the row source being the employee table) since the customer details on the main order form is NOT a sub form?

 

[Hayley Baxter]

Hello Tech

Just checking to see if the example I sent you back helped? Let me know then we can move onto the next problem

Hay